MRG_AL
asked on
Distinct Count in Access 2007 Query
I have a form which contains a continuous subform which contains a date field and need to count the number of DISTINCT dates per the memberID which is the link between the parent form and child. If the user enters the following dates how do I return the following result in a query or form?
MemberID Date
1 1/1/2011
1 1/1/2011
1 2/2/2011
2 3/3/2011
2 4/4/2011
2 4/4/2011
MemberID Result
1 2
2 2
I have been able to create a count based on the MemberID but it does not count the dates as distinct dates and if the date is entered twice I can only count it once for the member
Thank you very much.
MemberID Date
1 1/1/2011
1 1/1/2011
1 2/2/2011
2 3/3/2011
2 4/4/2011
2 4/4/2011
MemberID Result
1 2
2 2
I have been able to create a count based on the MemberID but it does not count the dates as distinct dates and if the date is entered twice I can only count it once for the member
Thank you very much.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
THANK YOU!!!!!!!!!!!!!
select memberid, count(distinct date)
from mytable
group by memberid