CODEWITHSUNDEEP
ms-accesscountfiltering
Vedran
Vedran

Reputation: 41

Counting/grouping records in Access query

I encountered an issue while trying to group records in a query.
What I need is - after I select DatePeriod from a comboBox - in an underlying subform to have a count of records for each employee within selected date period.

Overview

Note: for simplicity, I used two fields only.

Main Form has TabControl. Each Tab has a subform (Source: query). Each record in a query has Date (V_LOCATION_VISIT_DATE) and Employee (V_WORKER) fields. Each V_WORKER has hundreds of entries (link to Oracle db).

On a form I have cboDatePeriod (example: „16.05.2018-15.06.2018“).
After selecting a value from a cboDatePeriod, a code compares DatePeriod with dates in a queries and filters it.

A query has INNER JOIN to qryEmployees, since I want to count records for specific Employees.

Example of entries in a table DATA:

|V_WORKER       | V_LOCATION_VISIT_DATE  
| David Sylvian | 08.01.2018 14:38:21  
| David Sylvian | 08.01.2018 15:31:48  
| David Sylvian | 08.01.2018 16:22:07  
| Brian Eno     | 08.01.2018 17:33:07  
| Brian Eno     | 09.01.2018 16:18:50   
| Brian Eno     | 09.01.2018 17:09:21

Code without date: 

SELECT qryDATA.V_WORKER,Count(qryDATA.V_WORKER) AS CountOfV_WORKER
FROM qryEmployeesCZS DATA ON qryEmployeesCZS.FullName = qryDATA.V_WORKER
GROUP BY qryDATA.V_WORKER
HAVING (((qryDATA.V_WORKER)<>""));

Query result example: 

|V_WORKER       | CountOfV_WORKER              
| David Sylvian |  821   
| Brian Eno     |  92

It's how it should look alike, after applying DatePeriod filter. Let's move on.

Code with date included:
With Date included int function to remove Time stamp) instead having a single record for each Employee I have many records for each Employee, where each record is a count of records for a single day (grouped by V_WORKER and DAY). 

SELECT qryDATA.V_WORKER, Int([qryDATA].[V_LOCATION_VISIT_DATE]) AS V_LOCATION_VISIT_DATE, 
Count(qryDATA.V_WORKER) AS CountOfV_WORKER
FROM DATA 
INNER JOIN qryEmployeesCZS ON qryDATA.V_WORKER = qryEmployeesCZS.[FullName]
GROUP BY qryDATA.V_WORKER,Int([DATA].[V_LOCATION_VISIT_DATE])
HAVING (((qryDATA.V_WORKER)<>""));

Query result example (I used int function to remove timestamp): 

|    V_WORKER    | V_LOCATION_VISIT_DATE | CountOfV_WORKER   
| David Sylvian  |      08.01.2018       |        4   
| David Sylvian  |      09.01.2018       |        6   
| David Sylvian  |      10.01.2018       |        2   
| Brian Eno      |      11.01.2018       |        4   
| Brian Eno      |      12.01.2018       |        2   
| Brian Eno      |      15.01.2018       |        5   
| Brian Eno      |      16.01.2018       |        3

What I want:
After selecting Date period from a comboBox, I want to count a records grouped by V_WORKER - it should look like this: 

|    V_WORKER-  | CountOfV_WORKER   
| David Sylvian |     26    
| Brian Eno     |     17

I tried with a Textbox in a subform which counts records, but of course it counts all records, not grouped by employee.

What I'm thinking of (in a moment of desperation) to have two queries in a subform: queryOne will have all records with dates and no grouping, and a second one (source: queryOne) with removed Date field and grouping included.

Any ideas?

Upvotes: 1

Views: 2849

Answers (1)

Andre
Andre

Reputation: 27644

This is how aggregation and GROUP BY work.

If you group by date, you will get the count per date.
If you want to only group by employee to get the full count, then do that. Remove V_LOCATION_VISIT_DATE from the GROUP BY clause.

Which date would you want to get with the full count? The first? The last? Then use MIN() or MAX() aggregate functions for the date.

Edit from comment:

Then the date goes into the WHERE clause, like this:

SELECT qryEmployees.EmployeeName, Count(*) AS EmpCount
FROM (Data_Servisi INNER JOIN qryEmployees ON Data_Servisi.WORKER = qryEmployees.EmployeeName) INNER JOIN City ON qryEmployees.CityID = City.CityID
WHERE Data_Servisi.V_LOCATION_VISIT_DATE BETWEEN Forms!myForm!DateStart AND Forms!myForm!DateEnd
GROUP BY qryEmployees.EmployeeName

Upvotes: 0

Related Questions