473,322 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Count Unique Dates for Each User - Access query

newnewbie
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
6 11814
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
ADezii
8,834 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:

=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
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
8,834 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

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

Similar topics

1
by: Michael DeLawter | last post by:
Using Access 2002. I have a chart in a report that is currently based on a query in which the user enters the start and end date for the chart to display. Both the start and end dates have been...
5
by: Terri | last post by:
I have a form with a multi-select combo. I dynamically build a SELECT statement, open a report, and set the recordsource to my dynamic SELECT statement. I count the records returned in the report...
2
by: SJM | last post by:
I have a report that displays records of real estate properties. It is possible for each property to appear a number of times for various reasons. Each record however is unique. What I would like...
1
by: sunilkeswani | last post by:
Hi I am still new to access. I want to know how i can build a query which can display results from 4 different columns/fields Like. Field1 Field2 Field3 Field4 1 2 1 ...
18
by: PC Datasheet | last post by:
An Access user saw my name in a newsgroup and sent me a request for help on a project. As part of the project, a list of the dates in a month was needed. For anyone needing a list of dates in a...
22
by: MP | last post by:
vb6,ado,mdb,win2k i pass the sql string to the .Execute method on the open connection to Table_Name(const) db table fwiw (the connection opened via class wrapper:) msConnString = "Data Source="...
2
by: srusskinyon | last post by:
I need some help getting unique records from our database! I work for a small non-profit homeless shelter. We keep track of guest information as well as what services we have offered for...
12
by: petter | last post by:
Hi! I have two questions: one question that regards the COUNT-function, and one about how to display a month even if I don’t have any data for that month. I have an Access database where I want...
1
by: kummu4help | last post by:
hi, i am using mysql. i have 3 tables with following structures. these are not actual tables i am working on. table A id varchar(16),name varchar(255),InDate datetime table B id...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.