Date range count query (1 Viewer)

Charlie100

New member
Local time
Today, 22:15
Joined
Nov 2, 2021
Messages
12
Hi, I have a table with a range of dates within a field. If I wanted to run an ms access query to count occurrences of a criteria within another field within a specific date range is this possible?

My existing query will display all the rows within the date range but I'm looking just for a count number.

Example

Team A, Team C, Team C
Count the number of dates which fall between a start and end date for each team.

Example looking to achieve
Team A = 5 (numbers of dates falling between the date range)
Team B = 2
Team C = 1
My query for the
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:15
Joined
May 21, 2018
Messages
6,266
see example
 

Charlie100

New member
Local time
Today, 22:15
Joined
Nov 2, 2021
Messages
12
I only have minimal experience of using MS Access. On my query I have added two fields Team and dates

The date field I have added the criteria 》=[enter the start date] And《=[end date]

What do I put in the total field.?

Team displays - Group by
Date - Count


Error message displayed
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:15
Joined
May 7, 2009
Messages
16,092
copy and paste in SQL view of Query designer.
you need to replace the tablename and fieldnames according
to your table.

select [team], Count([dateField]) from yourTablename where [datefield] between [start date] and [end date]
group by [team];
 

Charlie100

New member
Local time
Today, 22:15
Joined
Nov 2, 2021
Messages
12
Never keyed an sql entry before so apologies.

My sql input displays an error.

SELECT
[Team field name],Count([Date field name]from[Table name][Date Field name]between[Start Date]and[End Date]group by [team name field];
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:15
Joined
May 7, 2009
Messages
16,092
My sql input displays an error.
is that the "real" tablename and fieldname you have?
if it is add some spaces between the wordings and you left out the "where" condition.

SELECT
[Team field name],Count([Date field name] from [Table name] where [Date Field name] between [Start Date] and [End Date] group by [team name field];
 

Charlie100

New member
Local time
Today, 22:15
Joined
Nov 2, 2021
Messages
12
I must be keying something wrong.
 

Attachments

  • 20211103_124352.jpg
    20211103_124352.jpg
    797.7 KB · Views: 159

Users who are viewing this thread

Top Bottom