SQL Select Distinct with MS Access
SQL Select Distinct with MS Access
(OP)
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!
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!
RE: SQL Select Distinct with MS Access
Your SQL code is correct as far as ANSI SQL (this forum) is concerned.
I'd post here:
forum701: Microsoft: Access Queries and JET SQL
Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: SQL Select Distinct with MS Access