Prev: Multiple Muliselect Listbox form update
Next: noticia
From: phd4212 on 6 Apr 2010 20:13
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.

From: Arvin Meyer [MVP] on 6 Apr 2010 22:30
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.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


"phd4212" <phd4212(a)discussions.microsoft.com> wrote in message
news:92547A8F-B585-4EDB-BBBF-E19E7AE8D1EE(a)microsoft.com...
> 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.
>


 | 
Pages: 1
Prev: Multiple Muliselect Listbox form update
Next: noticia