Count Records and Group by Month


G

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
 
Ad

Advertisements

J

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
 
M

Marshall Barton

GavinD said:
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
 
K

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top