By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,672 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,672 IT Pros & Developers. It's quick & easy.

Count Unique Dates for Each User - Access query

newnewbie
P: 54
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:

=SUM(IF(FREQUENCY(IF($C$2:$C$8957="username",IF($B $2:$B$8957<>"",MATCH($B$2:$B$8957,$B$2:$B$8957,0)) ),ROW($B$2:$B$8957)-ROW($H$2)+1)>0,1)) but I need to be able to do the same in Access....Please help!

Thanks,
Lena
May 30 '07 #1
Share this Question
Share on Google+
6 Replies

Rabbit
Expert Mod 10K+
P: 12,441
What about a select distinct and then a count?
May 30 '07 #2

P: 49
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

P: 49
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

ADezii
Expert 5K+
P: 8,750
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:

=SUM(IF(FREQUENCY(IF($C$2:$C$8957="username",IF($B $2:$B$8957<>"",MATCH($B$2:$B$8957,$B$2:$B$8957,0)) ),ROW($B$2:$B$8957)-ROW($H$2)+1)>0,1)) but I need to be able to do the same in Access....Please help!

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

newnewbie
P: 54
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

ADezii
Expert 5K+
P: 8,750
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

Post your reply

Sign in to post your reply or Sign up for a free account.