469,638 Members | 1,552 Online

# Count Unique Dates for Each User - Access query

Was looking all over to find an answer to my question, but did not. I have a database that lists different users entering multiple records every day. For me, the only way to see how many days the user was at work is to see if he/she was entering data on this particular date. E.g.:
User Date
Janet 01/01/2007
Janet 01/01/2007
Janet 01/01/2007
Janet 01/02/2007
Janet 01/02/2007
Lena 01/01/2007
Lena 01/02/2007
Lena 01/03/2007
Lena 01/03/2007
Lena 01/04/2007
Matt 01/01/2007
Matt 01/02/2007
Matt 01/03/2007
Matt 01/04/2007
Matt 01/07/2007
Mike 01/01/2007
Mike 01/01/2007
Mike 01/01/2007
Mike 01/01/2007
Mike 01/01/2007
Todd 01/03/2007
Todd 01/03/2007
Todd 01/03/2007
Todd 01/04/2007
Todd 01/04/2007

I need to calculate how many days in a particular period of time each user was in the system, working. Based on the above, in the month of January:

User Days in the system
Janet 2
Lena 4
Matt 5
Mike 1
Todd 2

I calculate "Days in the system" in Excel by using an array formula:

Thanks,
Lena
May 30 '07 #1
6 11293
Rabbit
12,516 Expert Mod 8TB
What about a select distinct and then a count?
May 30 '07 #2
SELECT table1.Name, Count(table1.date) AS CountOfdate
FROM table1
GROUP BY table1.Name;

ok - change the Table1 for the anme of you table where these names/dates are kept.

change Name for the name of the field carrying the names of the employees

change the word date for the name of the field containing the dates you want count

place all in to the query in SQl view and try it

If you are stuck shout we'll sort it

Good luck
May 30 '07 #3
SELECT table1.Name, Count(table1.date) AS CountOfdate
FROM table1
GROUP BY table1.Name;

ok - change the Table1 for the anme of you table where these names/dates are kept.

change Name for the name of the field carrying the names of the employees

change the word date for the name of the field containing the dates you want count

place all in to the query in SQl view and try it

If you are stuck shout we'll sort it

Good luck
May 30 '07 #4
8,800 Expert 8TB
Was looking all over to find an answer to my question, but did not. I have a database that lists different users entering multiple records every day. For me, the only way to see how many days the user was at work is to see if he/she was entering data on this particular date. E.g.:
User Date
Janet 01/01/2007
Janet 01/01/2007
Janet 01/01/2007
Janet 01/02/2007
Janet 01/02/2007
Lena 01/01/2007
Lena 01/02/2007
Lena 01/03/2007
Lena 01/03/2007
Lena 01/04/2007
Matt 01/01/2007
Matt 01/02/2007
Matt 01/03/2007
Matt 01/04/2007
Matt 01/07/2007
Mike 01/01/2007
Mike 01/01/2007
Mike 01/01/2007
Mike 01/01/2007
Mike 01/01/2007
Todd 01/03/2007
Todd 01/03/2007
Todd 01/03/2007
Todd 01/04/2007
Todd 01/04/2007

I need to calculate how many days in a particular period of time each user was in the system, working. Based on the above, in the month of January:

User Days in the system
Janet 2
Lena 4
Matt 5
Mike 1
Todd 2

I calculate "Days in the system" in Excel by using an array formula:

Thanks,
Lena
This response will solve your dilemma, but I'm pretty sure that the SQL Experts have a better solution. In any event, you can use this in the meantime.
1. Assuming your Table is named tblUserDatesWorked, and your Field Names are [Name] and [Date_Worked], create the following Query (we'll call it qryUniqueDaysWorked):
Expand|Select|Wrap|Line Numbers
1. SELECT DISTINCT tblUserDatesWorked.Name, tblUserDatesWorked.Date_Worked
2. FROM tblUserDatesWorked;
2. Create a second Query called qryCountOfUniqueDaysWorked using the following SQL Statement:
Expand|Select|Wrap|Line Numbers
1. SELECT [qryUniqueDaysWorked].Name, Count([qryUniqueDaysWorked].Date_Worked) AS Count_Of_Unique_Dates_Worked
2. FROM qryUniqueDaysWorked
3. GROUP BY [qryUniqueDaysWorked].Name;
3. NOTE: The above operation is nothing more than basing a 2nd Query on an Original one. This technique was originally suggested by Rabbit, I simply implemented it.
May 30 '07 #5
THANK YOU for all the tips.

Basically I wanted to avoid bulding queries on queries. But since I do not know VBA and I am not an SQL expert either...this is a way to go.

I built a query that selects distinct values and then another crosstab query calculating days in the system...so I can use it for my reports.

A little bulky, but I will work on it and we'll see.
Thanks!
May 31 '07 #6
8,800 Expert 8TB
THANK YOU for all the tips.

Basically I wanted to avoid bulding queries on queries. But since I do not know VBA and I am not an SQL expert either...this is a way to go.

I built a query that selects distinct values and then another crosstab query calculating days in the system...so I can use it for my reports.

A little bulky, but I will work on it and we'll see.
Thanks!
You're quite welcome!
May 31 '07 #7