SQL Select Distinct with MS Access

[sxp04rs]

Hi there,

I have a table of data similar to this:

staffId weekNumber hoursWorked
======================================
1 24 41
1 25 12
2 24 36
3 24 29
2 24 39
4 25 18

Each row represents a shift. What I need is to find out how many weeks a staff member has worked for us for. I think this would work:

SELECT COUNT(DISTINCT weekNumber) FROM tblName WHERE staffId=3

However Access does not allow COUNT DISTINCT. I have tried everything I can think of, can anyone shed any light?

Thanks!

 

[PHV]

JetSQL doesn't like the COUNT(DISTINT colname) predicate.
A workaround is to use a subquery:

SELECT staffId, CountDistinct
FROM (SELECT DISTINCT staffId,weekNumber FROM tblName) D

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:

SELECT staffId, COUNT(*) As CountDistinct
FROM (SELECT DISTINCT staffId, weekNumber FROM tblName) D
GROUP BY staffId

And if you want the total hours:

SELECT staffId, COUNT(*) As CountDistinct, SUM(tot) AS TotalHoursWorked
FROM (SELECT staffId, weekNumber, SUM(hoursWorked) AS tot
FROM tblName GROUP BY staffId, weekNumber) D
GROUP BY staffId

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sxp04rs]

Thanks PHV, that second example is actually exactly what I am trying to achieve. Question on your second example though, will that actually return a count of each distinct staffId, or just all records?

This is the problem I'm having. Without the Distinct keyword the COUNT function returns all records, but with the Distinct keyword the SUM function in the same query isn't totaling up every shift.

Does that make sense? I might be able to post some more examples if not

Thanks again!

 

[PHV]

will that actually return a count of each distinct staffId, or just all records?
Did you try it ?

 

[sxp04rs]

I thought I had, but apparently not as your example works perfectly, thanks very much! I've sort of been thrown in at the deep end with SQL, there's a lot more to learn than I thought...