Running Yearly Sum

[phd4212]

Hi

I have all of my data stored daily and I need to be able to query to find
the sum of the last year and 6 months data. I am at a loss how to do this.

I've tried running the query as both a select and a cross-tab query: I've
included the SQL codes



TRANSFORM Avg([Quantity Data].[Total Lines]) AS [AvgOfTotal Lines]
SELECT [Quantity Data].FillerInitials
FROM [Quantity Data] INNER JOIN [Roster - Main] ON [Quantity
Data].FillerInitials = [Roster - Main].FillerInitials
WHERE ((([Quantity Data].FillerInitials)="wjb") AND
((Format([Date]))<=Date()-365))
GROUP BY [Quantity Data].FillerInitials, Format([Date])
ORDER BY Format([Date],"yyyy")
PIVOT Format([Date],"yyyy");

SELECT [Quantity Data].FillerInitials, Avg([Quantity Data].[Total Lines]) AS
[AvgOfTotal Lines], Format([Date],"yyyy") AS [year]
FROM [Quantity Data] INNER JOIN [Roster - Main] ON [Quantity
Data].FillerInitials = [Roster - Main].FillerInitials
GROUP BY [Quantity Data].FillerInitials, Format([Date]), Format([Date],"yyyy")
HAVING ((([Quantity Data].FillerInitials)="wjb") AND
((Format([Date]))<=Date()-365))
ORDER BY Format([Date],"yyyy");


Thanks a lot.

 

[Arvin Meyer [MVP]]

You may need to run a separate (second) query to total the results of the
first. You can add queries to the query grid just as you would tables.