Date range count query (1 Viewer)

[Charlie100]

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]

Aggregate Queries in Microsoft access database

In Microsoft access database, you might want to perform operations on not just one single record, but on a group of records. You can simply create fields that perform operations per row or on every record.

accessdatabasetutorial.com

see example

 

[Charlie100]

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]

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]

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]

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]

I must be keying something wrong.

 

[eaglevelo]

I think you need a ) - bold here "[Team field name],Count([Date field name])..."