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
6 11814
What about a select distinct and then a count?
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
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
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. - Assuming your Table is named tblUserDatesWorked, and your Field Names are [Name] and [Date_Worked], create the following Query (we'll call it qryUniqueDaysWorked):
- SELECT DISTINCT tblUserDatesWorked.Name, tblUserDatesWorked.Date_Worked
-
FROM tblUserDatesWorked;
- Create a second Query called qryCountOfUniqueDaysWorked using the following SQL Statement:
- SELECT [qryUniqueDaysWorked].Name, Count([qryUniqueDaysWorked].Date_Worked) AS Count_Of_Unique_Dates_Worked
-
FROM qryUniqueDaysWorked
-
GROUP BY [qryUniqueDaysWorked].Name;
- 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.
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!
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!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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 ...
|
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...
|
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="...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |