Totaling by Month

[Michael P. Deslippe]

I must be brain dead this morning, but the answer isn't coming to me
easily this morning. I am using Access 2003. I have a database with
various names of people in it, various locations for the people and
dollar amounts for sales. I am writing a crosstab query and although
each day of the month has entries, for this query I want it rolled up my
month. e.g., January Sales, February Sales, etc.

How do I write the query so that is doesn't give me daily dates, but
aggregates the numbers by month?

---Mike

 

[John W. Vinson]

How do I write the query so that is doesn't give me daily dates, but
aggregates the numbers by month?

Include a calculated field:

SalesMonth: Format([SalesDate], "yyyy-mm")

and group by it.

 

[Ken Sheridan]

Mike:

You can format the date to return the month. You'll need to differentiate
between different years of course. You could have the years as 'row
headings', in which case the query would look something like this:

TRANSFORM COUNT(*)
SELECT YEAR(SaleDate), Location
FROM Sales
GROUP BY YEAR(SaleDate), Location
PIVOT FORMAT(SaleDate,"mmmm")
IN ("January","February","March", "April", <and so on>);

or you could return just one calendar year's data by means of a parameter:

TRANSFORM COUNT(*)
SELECT Location
FROM Sales
WHERE YEAR(SaleDate) = [Enter year:]
GROUP BY Location
PIVOT FORMAT(SaleDate,"mmmm")
IN ("January","February","March", <and so on>);

or you could include the years in the column headings, but this would return
a lot of columns over a period of years and you'd need to know the years
involved in advance as these have to be included in the IN clause's value
list to output them in the right order, unless you use the self-ordering
numeric format "yyyy mm" and omit the IN clause:

TRANSFORM COUNT(*)
SELECT Location
FROM Sales
GROUP BY Location
PIVOT FORMAT(SaleDate," mmmm yyyy")
IN ("January 2008"," February 2008", <and so on> "January 2009", " February
2009", <and so on>);

Ken Sheridan
Stafford, England

 

[Michael P. Deslippe]

How do I write the query so that is doesn't give me daily dates, but
aggregates the numbers by month?

Include a calculated field:

SalesMonth: Format([SalesDate], "yyyy-mm")

and group by it.

Yes sir, that was it. Thank you for the reply - just having a senior
moment today.

---Mike

 

[Michael P. Deslippe]

How do I write the query so that is doesn't give me daily dates, but
aggregates the numbers by month?

Include a calculated field:

SalesMonth: Format([SalesDate], "yyyy-mm")

and group by it.

Yes sir, that was it. Thank you for the reply - just having a senior
moment today.

---Mike

 

[My name]

Thank you for the response. That would work too. I apprciate yopur help.

---Mike

Mike:

You can format the date to return the month. You'll need to differentiate
between different years of course. You could have the years as 'row
headings', in which case the query would look something like this:

TRANSFORM COUNT(*)
SELECT YEAR(SaleDate), Location
FROM Sales
GROUP BY YEAR(SaleDate), Location
PIVOT FORMAT(SaleDate,"mmmm")
IN ("January","February","March", "April", <and so on>);

or you could return just one calendar year's data by means of a parameter:

TRANSFORM COUNT(*)
SELECT Location
FROM Sales
WHERE YEAR(SaleDate) = [Enter year:]
GROUP BY Location
PIVOT FORMAT(SaleDate,"mmmm")
IN ("January","February","March", <and so on>);

or you could include the years in the column headings, but this would
return
a lot of columns over a period of years and you'd need to know the years
involved in advance as these have to be included in the IN clause's value
list to output them in the right order, unless you use the self-ordering
numeric format "yyyy mm" and omit the IN clause:

TRANSFORM COUNT(*)
SELECT Location
FROM Sales
GROUP BY Location
PIVOT FORMAT(SaleDate," mmmm yyyy")
IN ("January 2008"," February 2008", <and so on> "January 2009", "
February
2009", <and so on>);

Ken Sheridan
Stafford, England

I must be brain dead this morning, but the answer isn't coming to me
easily this morning. I am using Access 2003. I have a database with
various names of people in it, various locations for the people and
dollar amounts for sales. I am writing a crosstab query and although
each day of the month has entries, for this query I want it rolled up my
month. e.g., January Sales, February Sales, etc.

How do I write the query so that is doesn't give me daily dates, but
aggregates the numbers by month?

---Mike