This is how the data lloks when it is done in an Excel spreadsheet. I am
trying to get the office weaned from doing data calculations in spreadsheets.
As you can see the last column is blank for the first 12 months as there is
no data to calculate. Starting in January of 2002, the three VOCs are added
and divided by 2000, then the 12 monthly totals are added, February does the
same but only calculates the previous 12 months. I guess the problem is that
I want each 12 month total to appear.
Year Month Average Monthly Volatile Air Emissions (lbs/month)
Benzene BTEX 1,2-DCA Total VOCs Rolling 12
2001 January 14.72491 248.29243 0.00000
February 6.57500 58.25449 0.00000
March 16.33233 131.08842 0.00000
April 0.00000 0.00000 0.00000
May 0.00000 0.00000 0.00000
June 2.12214 35.31234 0.00000
July 16.79956 299.4520 0.00000
August 7.50791 66.57018 0.00000
September 3.02020 27.37543 0.00000
October 3.13138 39.89874 0.66850
November 2.76997 46.56758 0.00000
December 0.00000 0.00000 0.00000
2002 January 6.46323 69.20632 0.00000 0.38686
February 2.80249 2.80249 0.00000 0.35914
March 3.87051 3.87051 0.00000 0.29553
April 6.09936 67.09297 0.00000 0.32907
May 10.84175 163.16840 0.27104 0.41066
June 8.19627 88.23039 0.00000 0.43712
July 12.01886 108.16973 0.00000 0.34148
August 0.00000 0.00000 0.00000 0.30819
September 0.00000 0.00000 0.00000 0.29450
October 0.00000 0.00000 0.00000 0.27455
November 0.00000 0.00000 0.82287 0.25127
December 0.00000 0.00000 0.00000 0.72768
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!
KARL DEWEY said:
Does this do what you want?
SELECT Format([Sample Date],"mmmm yyyy") AS [Month & Year], [Benzene Air
Emissions (lbs/hr)]*[Hours] AS Benzene, [BTEX Air Emissions (lbs/hr)]*[Hours]
AS BTEX, [1,2 DCA Air Emissions (lbs/hr)]*[Hours] AS [1,2 DCA],
([Benzene]+[BTEX]+[1,2 DCA])/2000 AS [Monthly Total]
FROM [Monthly Air Emissions Calculations]
WHERE ((([Monthly Air Emissions Calculations].[Sample Date]) Between
DateAdd("yyyy",-1,Date()) And Date()))
ORDER BY Format([Sample Date],"yyyymm");
Larry G. said:
SELECT
[Monthly Air Emissions Calculations].[Sample Date],
[Benzene Air Emissions (lbs/hr)]*[Hours] AS Benzene,
[BTEX Air Emissions (lbs/hr)]*[Hours] AS BTEX,
[1,2 DCA Air Emissions (lbs/hr)]*[Hours] AS [1,2 DCA],
([Benzene]+[BTEX]+[1,2 DCA])/2000 AS [Monthly Total]
FROM [Monthly Air Emissions Calculations];
So what I want to do I THINK is a DSum() for the Monthly Total, but ONLY the
last twelve months at a time.
--
Never give up, the answer IS out there, it just takes a while to find it
sometimes!
:
Post your SQL. Open the query in design view and click on menu VIEW - SQL
View. Hightlight, copy and paste in a post.
:
I am going to try posting this question again in hopes of getting a reponse
from someone.
I have a query with for fields Date Sampled, Benzene, BTEX, and 1,2 DCA.
This query pulls data for the last 5 years. I would like a column titled 12
Month Rolling Total that will take only the sum of the three analyticals for
the last twelve months only, NOT the entire column.
Is there a way to do it?
