Multiple subtotals - Month/Year

  • Thread starter Thread starter John
  • Start date Start date
J

John

I have a report that has detail records showing individual purchases.
I need to have subtotals for each month and each year in the report.
When I use the grouping menu I can do monthly or yearly subtotals, but
not both. I would appreciate any help with this problem.

Thanks!
 
You could calculate the months and year in a parameter query and base the
report on the query. In the query, first calculate the month # so the months
can be sorted normally (Jan-Feb-Mar, etc.). The user might be asked to enter
a particular month or press enter for all the months. User must also select
a particular year. Follow the example below:

1st Field: Month #: Month([DateOrdered]) (or whatever your date field is
named).
Sort: Ascending
Criteria: [Enter Month (1-12) or Press Enter for All]
Or: [Enter Month (1-12) or Press Enter for All] Is Null

2nd Field: Order Month: Format([DateOrdered],"mmmm")
Sort: None
Criteria: None

3rd Field: Order Year: Format([DateOrdered],"yyyy")
Sort: None
Criteria: [Enter Year (4 Digits)]


Con Giacomini
 
Thanks for your response, but I don't think I clearly defined the
problem. The report is based off of a query having two user input
parameters: start_date and end_date. The report then lists all
purchases with purchase_date between start_date and end_date in the
detail section. I need to have subtotals at each change in month and
each change in year. I can get the report (using the grouping menu)
to print subtotals after each month or each year, but not both. Here
is what I am trying to do:

Purchase_Date Purchase details......
1/2/02 ....
1/5/02 ....
Jan Total xxxx <--- Can get this using group on month

<same for each month>

12/15/02 .....
12/20/02 .....
Dec Total xxxxx
2002 Total yyyyy <--- Can't get this one to work

1/4/03 .....
1/15/03 .....
Jan Total xxxxx

<continues until end_date>

Again - any help is appreciated!


Con Giacomini said:
You could calculate the months and year in a parameter query and base the
report on the query. In the query, first calculate the month # so the months
can be sorted normally (Jan-Feb-Mar, etc.). The user might be asked to enter
a particular month or press enter for all the months. User must also select
a particular year. Follow the example below:

1st Field: Month #: Month([DateOrdered]) (or whatever your date field is
named).
Sort: Ascending
Criteria: [Enter Month (1-12) or Press Enter for All]
Or: [Enter Month (1-12) or Press Enter for All] Is Null

2nd Field: Order Month: Format([DateOrdered],"mmmm")
Sort: None
Criteria: None

3rd Field: Order Year: Format([DateOrdered],"yyyy")
Sort: None
Criteria: [Enter Year (4 Digits)]


Con Giacomini






John said:
I have a report that has detail records showing individual purchases.
I need to have subtotals for each month and each year in the report.
When I use the grouping menu I can do monthly or yearly subtotals, but
not both. I would appreciate any help with this problem.

Thanks!
Click to expand...
Click to expand...
 
You can set the first sorting field to
=Year([Purchase_Date])
set the second sorting field to
=Month([Purchase_Date])

--
Duane Hookom
MS Access MVP


John said:
Thanks for your response, but I don't think I clearly defined the
problem. The report is based off of a query having two user input
parameters: start_date and end_date. The report then lists all
purchases with purchase_date between start_date and end_date in the
detail section. I need to have subtotals at each change in month and
each change in year. I can get the report (using the grouping menu)
to print subtotals after each month or each year, but not both. Here
is what I am trying to do:

Purchase_Date Purchase details......
1/2/02 ....
1/5/02 ....
Jan Total xxxx <--- Can get this using group on month

<same for each month>

12/15/02 .....
12/20/02 .....
Dec Total xxxxx
2002 Total yyyyy <--- Can't get this one to work

1/4/03 .....
1/15/03 .....
Jan Total xxxxx

<continues until end_date>

Again - any help is appreciated!


"Con Giacomini" <[email protected]> wrote in message
You could calculate the months and year in a parameter query and base the
report on the query. In the query, first calculate the month # so the months
can be sorted normally (Jan-Feb-Mar, etc.). The user might be asked to enter
a particular month or press enter for all the months. User must also select
a particular year. Follow the example below:

1st Field: Month #: Month([DateOrdered]) (or whatever your date field is
named).
Sort: Ascending
Criteria: [Enter Month (1-12) or Press Enter for All]
Or: [Enter Month (1-12) or Press Enter for All] Is Null

2nd Field: Order Month: Format([DateOrdered],"mmmm")
Sort: None
Criteria: None

3rd Field: Order Year: Format([DateOrdered],"yyyy")
Sort: None
Criteria: [Enter Year (4 Digits)]


Con Giacomini






John said:
I have a report that has detail records showing individual purchases.
I need to have subtotals for each month and each year in the report.
When I use the grouping menu I can do monthly or yearly subtotals, but
not both. I would appreciate any help with this problem.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Duane. That worked fine. John

Duane Hookom said:
You can set the first sorting field to
=Year([Purchase_Date])
set the second sorting field to
=Month([Purchase_Date])

--
Duane Hookom
MS Access MVP


John said:
Thanks for your response, but I don't think I clearly defined the
problem. The report is based off of a query having two user input
parameters: start_date and end_date. The report then lists all
purchases with purchase_date between start_date and end_date in the
detail section. I need to have subtotals at each change in month and
each change in year. I can get the report (using the grouping menu)
to print subtotals after each month or each year, but not both. Here
is what I am trying to do:

Purchase_Date Purchase details......
1/2/02 ....
1/5/02 ....
Jan Total xxxx <--- Can get this using group on month

<same for each month>

12/15/02 .....
12/20/02 .....
Dec Total xxxxx
2002 Total yyyyy <--- Can't get this one to work

1/4/03 .....
1/15/03 .....
Jan Total xxxxx

<continues until end_date>

Again - any help is appreciated!


"Con Giacomini" <[email protected]> wrote in message
You could calculate the months and year in a parameter query and base the
report on the query. In the query, first calculate the month # so the months
can be sorted normally (Jan-Feb-Mar, etc.). The user might be asked to enter
a particular month or press enter for all the months. User must also select
a particular year. Follow the example below:

1st Field: Month #: Month([DateOrdered]) (or whatever your date field is
named).
Sort: Ascending
Criteria: [Enter Month (1-12) or Press Enter for All]
Or: [Enter Month (1-12) or Press Enter for All] Is Null

2nd Field: Order Month: Format([DateOrdered],"mmmm")
Sort: None
Criteria: None

3rd Field: Order Year: Format([DateOrdered],"yyyy")
Sort: None
Criteria: [Enter Year (4 Digits)]


Con Giacomini






I have a report that has detail records showing individual purchases.
I need to have subtotals for each month and each year in the report.
When I use the grouping menu I can do monthly or yearly subtotals, but
not both. I would appreciate any help with this problem.

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top