Tallying query

[cj-1289]

Hi, I'm having difficulties trying to get a table ("skills") which lists all employee activities/skills joined with employee-specific queries (e.g. "david_count") of another table which logs every activity completed by any staff member ("completed"). The intention is to create a query ("matrix") of every activity with a tally of every time that each staff member has performed it, lookling a little like this:

ACTIVITY / DAVID / MARTIN / STEVEN
Cleaning 54 34 56
Drying 45 56 78
Updates
Lifting 67

"Skills" looks like this (there's over 100 records in the actual list):
SKILL
Cleaning
Drying
Updates
Lifting

"Completed" looks like this (and there are 1000s of records like this one):
ACTIVITY / EMPLOYEE / DATE
Cleaning / David / 03/06/2011

And David_count queries Completed with:
SELECT DISTINCT COMPLETED.ACTIVITY, COUNT(ACTIVITY) AS DAVID FROM COMPLETED WHERE COMPLETED.EMPLOYEE= "David"
GROUP BY ACTIVITY, EMPLOYEE

to produce David's data for the matrix, i.e.:

ACTIVITY / DAVID
Cleaning 54
Drying 78
Lifting 67

(this doesn't produce a record for any activities on 'skills' that David hasn't done but the query is really only a stepping stone so that doesn't matter at this stage).

So all I need to do now is join every employee's "_count" query with the skills list to produce the matrix. I've used this:
SELECT Skills.*, David_Count.David, Steven_count.Steven FROM (Skill INNER JOIN David_Count ON Skills.Skill = David_count.Activity) INNER JOIN Steven_Count ON Steven_Count.Activity = David_Count.Activity;

to produce this:
ACTIVITY / DAVID / STEVEN
Cleaning 54 56
Drying 45 67

But it will only list the activities that have actually been done by both of the employees (I want to list every single activity in "skills" and tally against them, regardless of whether any employee has actually done that activity) and when I try to use a full join it tells me that I have a syntax error with my from statement. If I get this to work for 2 employees I'll chain joins for the rest of the employees (there's about 20) using a VBA loop.

So could anybody help me to reach the result that I want? I think full joins must be the answer but I can't get it to work, or perhaps there's an entirely different approach that I haven't considered. I did consider just using VBA code to put the data together in a table whenever it's needed but would prefer just to have a query to open for the sake of speed. Thanks in advance

 

[Beetle]

Hopefully your example data is not a true representaion of the actual data in your table. In the Completed table you should be storing the SkillID and EmployeeID, not the actual names. Also, Date is a Reserved word in Access and should not be used as a field name. For more on Reserve words see;

http://allenbrowne.com/AppIssueBadWord.html

Moving on, you should not be creating employee specific queries. Based on your posted example data, all you need is two simple queries.

First, join the Skills table to the Completed table using a Left join so you will get all records from Skills whether the skill has been completed or not. Call it , for example, qrySkillsCompleted (I have changed the name of your Date field in this example);

Select Skills.Skill, Comlpeted.Employee, Completed.CompletedDate From Skills Left Join Completed On Skills.Skill = Completed.Activity;

Then all you need is a simple Crosstab query based on the above query;

Transform Count(qrySkillsCompleted.CompletedDate) As CountOfCompletedDate
Select qrySkillsCompleted.Skill, Count(qrySkillsCompleted.CompletedDate) As Total
From qrySkillsCompleted
Group By qrySkillsCompleted.Skill
Pivot qrySkillsCompleted.Employee;

This should give the results you are looking for without all the additional brain damage of creating a separate query for every employee and some unnecessary VBA loop.

 

[cj-1289]

Thanks a lot Sean. The date field is actually called Activity_Date and the test data is nothing like the actual data. I wasn't wanting Activity_Date involved at all, so I modified your crosstab query to:

TRANSFORM Count(qrySkillsCompleted.Skill) AS CountOfActivities
SELECT qrySkillsCompleted.Skill
FROM qrySkillsCompleted
GROUP BY qrySkillsCompleted.Skill
PIVOT qrySkillsCompleted.Employee;

to produce the result that I was after. Brilliant, especially for saving me an excursion into VBA and a ton of generated queries - cheers again.