Count Records and Group by Month

[GavinD]

I am trying to create an Access 2003 query to count the number of projects
started in a month.

My table looks like this (Dates are in UK format)

ProjectID Start_Date
Project1 15/01/2009
Project2 17/01/2009
Project3 02/02/2009
Project4 28/04/2009
Project5 01/06/2009
Project6 10/06/2009


The result I am looking for should look like this:

Month/Year CountOfProjects
January 2009 2
February 2009 1
April 2009 1
June 2009 2

Any suggestions?

Cheers

Gavin

 

[John Spencer MVP]

SELECT Format(Start_Date,"mmmm yyyy") as MonthYear
, Count(ProjectId) as CountProjects
FROM YourTable
GROUP BY Format(Start_Date,"mmmm yyyy")
, Format(Start_Date,"yyyymm")
ORDER BY Format(Start_Date,"yyyymm")

In Query design view
-- Add your table
-- Add fields Start_Date twice and ProjectID once
-- Change first Start_Date to read
MonthYear: Format(Start_Date,"mmmm yyyy")
-- Change second start date to read
Format(Start_Date,"mmmm yyyy") and uncheck SHOW field
Set Sort BY to Ascending
-- Select View: Totals from Menu
-- Change GROUP BY to COUNT for ProjectID


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Marshall Barton]

I am trying to create an Access 2003 query to count the number of projects
started in a month.

My table looks like this (Dates are in UK format)

ProjectID Start_Date
Project1 15/01/2009
Project2 17/01/2009
Project3 02/02/2009
Project4 28/04/2009
Project5 01/06/2009
Project6 10/06/2009


The result I am looking for should look like this:

Month/Year CountOfProjects
January 2009 2
February 2009 1
April 2009 1
June 2009 2

SELECT DateSerial(Year(datefield),Month(datefield),1)
As [Month/Year],
Count(*) As CountOfProjects
FROM table
GROUP BY DateSerial(Year(datefield),Month(datefield),1)

Set the form/report [Month/Year] text box's Format property
to:
mmmm yyyy

 

[ken]

You can format the Start_Date and group the query by the formatted
values:

SELECT FORMAT(Start_Date, "mmmm yyyy") AS Month_Year,
COUNT(*) AS CountOfProjects
FROM YourTable
GROUP BY FORMAT(Start_Date, "mmmm yyyy");

If you want to sort the result table chronologically you'll need to
introduce additional columns:

SELECT YEAR(Start_Date) AS StartYear,
MONTH(Start_Date) AS StartMonth,
FORMAT(Start_Date, "mmmm yyyy") AS Month_Year,
COUNT(*) AS CountOfProjects
FROM YourTable
GROUP BY FORMAT(Start_Date, "mmmm yyyy"),
YEAR(Start_Date), MONTH(Start_Date)
ORDER BY YEAR(Start_Date), MONTH(Start_Date);

Ken Sheridan
Stafford, England

 

[GavinD]

Many thanks John, just what I was looking for.

Cheers
Gavin