Distinct Count in Access 2010 (1 Viewer)

[dragnsteph]

I'm trying to write a query to get a count of Volunteers under a certain Job Code for a given year - problem comes in that a single Volunteer may record hours multiple times under a Job Code in a given Fiscal Year. I can't seem to get "Unique Values" to work, so I'm hoping for some assistance. From my research it looks like I need a two-part query but (as a newbie) I'm not quite sure how to write that. I have two queries, one that works and one that doesn't.

This one counts total amount of hours volunteered under a given Job Code, it works:

SELECT tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup, Sum(tblHoursWorked.HoursWorked) AS [Sum Of HoursWorked]
FROM tblHoursWorked
GROUP BY tblHoursWorked.FiscalYear, tblHoursWorked.JobCodeLookup;
HAVING (((tblHoursWorked.FiscalYear)=[Enter Year:]));

This one attempts to count number of Volunteers that worked under each Job Code in a given year - it instead counts number of entries under that job code. What I think I need to do is count unique instances of the NamesIDFK, but I can't seem to get that to work.

SELECT DISTINCT tblHoursWorked.[JobCodeLookup]
FROM tblHoursWorked
GROUP BY tblHoursWorked.[JobCodeLookup];

If I could get help 1) correcting my second query and 2) putting them into one query so I can use them in a report, that would be great.

Thanks,
Stephanie

 

[plog]

Can you post sample data from your table and then based on that sample data what you expect your ultimate query to generate? Be sure to include table/field names and enough data to cover all cases.

 

[dragnsteph]

I think this is what you're looking for, see the attached screenshot of part of the table (tblHoursWorked). Field names (I realized that some of them were cut off) are HoursID, HoursWorked, MonthWorked, YearWorked, FiscalYear, JobCodeLookup, NamesIDFK -- NamesIDFK is the identifier for the volunteer in my tblVolunteers table.

What I would like returned, ultimately - the query asks for a fiscal year, then based on that parameter, returns:

Job Code / # of Volunteers / Total Hours Volunteered for that Job Code

Volunteers can be counted under more than one code, but should only be counted once under each code. That's what I'm having trouble with.

 

[plog]

That's half of what I want. Now, based on that data, show me the expected data to be returned by your ultimate query.

 

[dragnsteph]

Oh, I see. Ok.

Assume 2015 entered for the parameter, then the results would be:

A-AVC-SCA / 1 / 12
A-CM-EFK / 2 / 47
A-FZ / 1 / 12

 

[plog]

Ok, got it. The best way will take 2 sub-queries. Here's the SQL for the first:

SELECT tblHoursWorked.JobCodeLookup, tblHoursWorked.NamesIDFK, Sum(tblHoursWorked.HoursWorked) AS WorkerHours
FROM tblHoursWorked
WHERE (((tblHoursWorked.FiscalYear)=[Enter Year]))
GROUP BY tblHoursWorked.JobCodeLookup, tblHoursWorked.NamesIDFK;

Name that query 'JobCodeTotals_sub1'. It groups all the data by JobCode/Worker and is the basis for all other queries so that you only have to enter the parameter once.

SELECT JobCodeTotals_sub1.JobCodeLookup, Count(JobCodeTotals_sub1.NamesIDFK) AS UniqueWorkers
FROM JobCodeTotals_sub1
GROUP BY JobCodeTotals_sub1.JobCodeLookup;

Name that 'JobCodeTotals_sub2'. It gets the count of unique workers for each Job Code. Lastly, this SQL will tie it all together and give you the results you want:

SELECT JobCodeTotals_sub1.JobCodeLookup, JobCodeTotals_sub2.UniqueWorkers, Sum(JobCodeTotals_sub1.WorkerHours) AS TotalHours
FROM JobCodeTotals_sub1 INNER JOIN JobCodeTotals_sub2 ON JobCodeTotals_sub1.JobCodeLookup = JobCodeTotals_sub2.JobCodeLookup
GROUP BY JobCodeTotals_sub1.JobCodeLookup, JobCodeTotals_sub2.UniqueWorkers;

 

[dragnsteph]

Thanks! Appreciate your help, that worked