Using an Access table for input processing

[DonnyBoy]

[COLOR=var(--black-200) !important][COLOR=var(--black-500) !important]0



[/COLOR]
[/COLOR]
I need to process through a list of technical skills one by one and get a count of the number of developers we have in 3 locations who have that skill. For example, Skill = "Java". How many persons have this skill listed in their resume.
I have 2 tables:
Skills: contains a single column listing skills - "Java" for example Resources: contains 4 columns, Resource-ID, Name, Location, and a long text field called "Resume" containing text of their resume.
If this were a single skill I would process the SQL something like below (SQL syntax not exact) SELECT count FROM [Resources] WHERE ([Resources].[Resume] Like "Java*");


I want to process the Skills table serially printing the "Skill" and the count in each location.
Help appreciated
I've only used Access as a DB for single record retrieval, never using table values as input to loop through a process. I suspect that this is a simple execution in MS Access.

• ms-access

[orange]

I suggest you take a few sample RESUME data from your table and post it for readers to get some flavor of your context. And a list of the Skills involved.

[Welshgasman]

Possibly group by skill and location.

[June7]

Correct link to cross-post https://stackoverflow.com/questions/...96608#75496608

[DonnyBoy]

Very well, I made a sample DB, attached. I also added a sample query for the search if I were looking a single skill. In my example, I want to walk through each of the skills listed in the Skill table and list out a count.

SELECT Resource.PID, Resource.Name, Resource.Location, Resource.[Resume Text]
FROM Resource
WHERE Resource.[Resume Text] Like "*" &'java'& "*";

[June7]

Have you reviewed solution suggested on your StackOverflow question?

[DonnyBoy]

Yes I reviewed but it did not answer my question. I posted that same response. I also did not have the oppty to upload an Access DB as I do here. That artifact helps illustrate the question.

[June7]

Exactly how does the two answers not resolve issue? Both suggestions produce the output you require: "count of the number of developers we have in 3 locations who have that skill"

My approach used LIKE operator, Albert used InStr() function. Both perform a pattern match search.

There was no image in the response you posted which was an inappropriate use of the answer feature. It also specifies a different requirement: "desired result is a simple matrix listing counts of skills by resource and where they are" - this says 'counts of skills' instead of 'counts of developers'. A 'resource' is a developer?

[mike60smart]

Hi

Use the following as the criteria.

You can then use it for whatever criteria needed

Like "*" & [Enter Skill Required] & "*"

[xps35]

Try something like

SELECT Resource.Location, Skill.[Normalized skill], Count(Resource.ID) AS CountOfPlantSkillFROM Resource, Skill
WHERE (((Resource.[Resume Text]) Like "*" & [Normalized skill] & "*"))
GROUP BY Resource.Location, Skill.[Normalized skill];

[DonnyBoy]

Try something like

SELECT Resource.Location, Skill.[Normalized skill], Count(Resource.ID) AS CountOfPlantSkillFROM Resource, Skill
WHERE (((Resource.[Resume Text]) Like "*" & [Normalized skill] & "*"))
GROUP BY Resource.Location, Skill.[Normalized skill];

Step 1: Create a query that executes the below SQL to get a counts (here named "Step1Query")
SELECT Skills.SkillName, Resources.Location,
Sum(Abs([Resume] Like '*' & [SkillName] & "*")) AS DevCt
FROM Skills, Resources
GROUP BY Skills.SkillName, Resources.Location;


Step 2: Create a second query that uses the Step 1 query as input. (you can do this via the wizard)
TRANSFORM Sum(Step1Query.DevCt) AS SumOfDevCt
SELECT Skills.SkillName, Resources.Location,
Sum(Abs([Resume] Like '*' & [SkillName] & "*")) AS DevCt
FROM Skills, Resources
GROUP BY Skills.SkillName, Resources.Location
PIVOT Step1Query_qry.[Location];


Result lists out a matrix form