Running 12 Month Sum

[Guest]

I have a report that needs to sum the total BTEX removed from a system just
within the last 12 months. So I have a date field, and a year field, the date
field is formatted to "mmmm" to display just the month. So if the month on
the report is July I need it to total, June of last year through July of this
year.

Thanks in advance!

(Are you guys tired of me yet?)

 

[John Vinson]

I have a report that needs to sum the total BTEX removed from a system just
within the last 12 months. So I have a date field, and a year field, the date
field is formatted to "mmmm" to display just the month. So if the month on
the report is July I need it to total, June of last year through July of this
year.

Thanks in advance!

(Are you guys tired of me yet?)

Do you have SEPARATE date and year fields...??? Why? Any date includes
the year!

Bear in mind, in a Date/Time field, what's stored is just a number: a
count of days and fractions of a day (times) since midnight, December
30, 1899. The format is COMPLETELY IRRELEVANT - it merely controls how
that number is displayed. For example:
?cdbl(now)
38730.4588078704
You could display this value with a "MMMM" format and see January, or
a yyyy format and see 2006, or a mm/dd/yyyy hh:nn:ss format and see
01/13/2006 11:00:41.

I suspect you need to remove your redundant Year field, unless it
serves some other purpose I don't understand. A criterion on your
datefield of

DateAdd("yyyy", -1, Date())

will return all records going back a year from today; a criterion of

DateSerial(Year(Date) - 1, Month(Date), 1)

will return all records from the first day of the month one year ago
(e.g. today all records where the date is greater tha 1 Jan 2005; if
you run the query next month, greater than 1 Feb 2005).

John W. Vinson[MVP]

 

[Guest]

I guess I was a little vague, this is for a report. I use the date field from
the query twice, once formatted as the year, so I can sort the records by the
year and also use it as a column header.

If within the report itself I want to sum a column named [BTEX Air Emissions
(lbs)] for the last 12 months. Could I do that within the report, or would it
be best to do it within a query?

 

[John Vinson]

If within the report itself I want to sum a column named [BTEX Air Emissions
(lbs)] for the last 12 months. Could I do that within the report, or would it
be best to do it within a query?

I'm not sure I understand. You're displaying records outside the year,
but you only want the sum for the last 12 months? or do you just want
to display records for the past 12 months?

If you just want a textbox with the sum, a DSum() function might be
the ticket; but if you want to *first* select only records for the
past 12 months to be displayed on the report, and sum all of those
records, you should be able to do the sum in a Form or section footer.

John W. Vinson[MVP]

 

[Guest]

Within the report, I have all the records, one for each month, grouped by
year. So eventhough records may be grouped within 2001 and 2002, I still need
the sum for 12 months total, regardless of what YEAR that month falls into.
If I group the report by MONTH then all the Jauarys will be together and so
on, not what I want.

This is how the report is laid out:

Year - Date/Time field formatted as "yyyy" to display the year of the record
grouping.

Month - based on the same date/time field but formatted "mmmm" to only
display the Month of the record, displayed in ascending order.

Benzene - a numeric field based on a calculation in the query

BTEX - same as above

1,2 DCA - same as above

Rolling 12 Month Emissions - what this field needs to do is sum the previous
12 BTEX fields above it so that the State agency can see if at anytime within
the last 12 months we have exceeded our 11.5 ton limit.
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!

If within the report itself I want to sum a column named [BTEX Air Emissions
(lbs)] for the last 12 months. Could I do that within the report, or would it
be best to do it within a query?

I'm not sure I understand. You're displaying records outside the year,
but you only want the sum for the last 12 months? or do you just want
to display records for the past 12 months?

If you just want a textbox with the sum, a DSum() function might be
the ticket; but if you want to *first* select only records for the
past 12 months to be displayed on the report, and sum all of those
records, you should be able to do the sum in a Form or section footer.

John W. Vinson[MVP]