3 Queries but need just 1 report

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

3 Queries but need just 1 report

Author

Topic

witsendestate
Starting Member

1 Post

Posted - 2012-08-12 : 20:57:35

Hi Team,

I have the following 3 queries that each generate a report.. What Im trying to do is create 1 report that has all 3 queries on it..

Each query is for a list of pickerids which are within 1 of 3 teams.. I need to keep these separate to compare results.

Here are the 3 queries.. only the pickerid's are different in each query... I execute these queries from a macro with Access 2007.

Appreciate your help

Query 1.

SELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate
FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight
FROM mergeddatabase
where pickerid in (11, 16, 19, 22, 26, 29, 37, 39, 40, 42, 44, 46, 47, 49)
GROUP BY pickerid,currentdate,pickerhours,pickername) AS [%$##@_Alias]
WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1))
GROUP BY pickerid, pickername
ORDER BY 4 DESC;

Query 2.
SELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate
FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight
FROM mergeddatabase
WHERE PICKERID IN (2,3,5,8,9,13,15,17,23,27,28,32,34,50 )
GROUP BY pickerid,currentdate,pickerhours,pickername) AS [%$##@_Alias]
WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1))
GROUP BY pickerid, pickername
ORDER BY 4 DESC;

Query 3.
SELECT Pickerid, Pickername, ROUND(SUM(totalweight), 2) AS PickersTotalWeight, SUM(totalweight)/ROUND(SUM(pickerhours)) AS HourlyRate
FROM (SELECT pickerid,currentdate,pickerhours,pickername,SUM(currentweight) AS totalweight
FROM mergeddatabase
WHERE PICKERID IN (2,3,5,8,9,13,15,17,23,27,28,32,34,50 )
GROUP BY pickerid,currentdate,pickerhours,pickername) AS [%$##@_Alias]
WHERE ((currentdate between [Enter first date:] AND [Enter last date:]) and (pickerhours > 0.1))
GROUP BY pickerid, pickername
ORDER BY 4 DESC;

Regards
Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources