Queries for Monthly Summary by Year

  • Thread starter Thread starter taft71
  • Start date Start date
T

taft71

I have a query which has many fields including, Completedate, Submitter,
SARtype. What I would like to do is create a query which summarizes
(groups) by MMYYYY, the Submitter with the various SARtypes such as:
March 2005 SARTYPE1 SARTYPE2 SARTYPE3 ETC

Submitter 1 3 1
Submitter1 2 0 1
Sumbitter2 0 1 1
etc
Totals by Month
Grand Totals all MOnths
AND MAKE THIS INTO A REPORT. eACH MONTH MUST SHOW SEPARATELY
I would be happy to explain in detail if you e-mail me. Thanks.
 
taft71 said:
I have a query which has many fields including, Completedate, Submitter,
SARtype. What I would like to do is create a query which summarizes
(groups) by MMYYYY, the Submitter with the various SARtypes such as:
March 2005 SARTYPE1 SARTYPE2 SARTYPE3 ETC

Submitter 1 3 1
Submitter1 2 0 1
Sumbitter2 0 1 1
etc
Totals by Month
Grand Totals all MOnths
AND MAKE THIS INTO A REPORT. eACH MONTH MUST SHOW SEPARATELY
I would be happy to explain in detail if you e-mail me. Thanks.
Click to expand...

You could probably use a crosstab query to accomplish this. Read the
Access Help on crosstab queries.
 
By the way, when I tried the crosstab the month year came out MARCH, 2005.
How can I make it 03/2005 so it sorts correctly?
 
taft71 said:
By the way, when I tried the crosstab the month year came out MARCH, 2005.
How can I make it 03/2005 so it sorts correctly?
Click to expand...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can format it:

Format(date_column, "mm/yyyy")

Since Format() returns a string it won't sort correctly if the dates
cross a year boundary. To correctly sort it would have to be formatted
like this:

Format(date_column, "yyyy/mm")

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQjOXP4echKqOuFEgEQKSLACfTdBIuAPEOJ8RxnA5cSUuszfhG+MAn3kq
xRzUAHI0hDdyMHA6Npz2dvKL
=eMKY
-----END PGP SIGNATURE-----
 
Sorry for this question but:
Format(date_column, "yyyy/mm")

Does the date portion of the format statement represent the field name? I
tried to enter this in the format of a field on a report but it doesnt
work. My field name is BSAComplete. Just where does it go?
 
Pardon me for jumping in.

This is supposed to be a calculated field in the query.
Field: Format(BSAComplete,"yyyy/mm")
 
Back
Top