Creating a Report from multiple queries

[np3il]

Hello I am trying to create a report from multiple queries.

The queries are already created and working.

How can I combine these queries into One Report?

Queries:
1. Day Shift Avg Totals
2. Night Shift Avg Totals
3. All Shift Avg Totals
4. Location1 Totals
5. Location2 Totals
6. Location1 Day Totals
7. Location1 Night Totals
8. Location2 Day Totals
9. Location2 Night Totals

Desired Report:
Gross Profit Avg Hours Avg ...
Day Shift Avg Totals $999.99 $999.99 99 99 ...
Night Shift Avg Totals $999.99 $999.99 99 99 ...
All Shift Avg Totals $999.99 $999.99 99 99 ...
Location1 Totals $999.99 $999.99 99 99 ...
Location2 Totals $999.99 $999.99 99 99 ...
Location1 Day Totals $999.99 $999.99 99 99 ...
Location1 Night Totals $999.99 $999.99 99 99 ...
Location2 Day Totals $999.99 $999.99 99 99 ...
Location2 Night Totals $999.99 $999.99 99 99 ...

All suggestions are welcome.

Thanks

 

[dhookom]

I believe the best solution would be to use a union query since it looks like the output of each query is similar. However you haven't provided any SQL statements so I can't be sure.

Another method which I think would be less desirable would be multiple subreports on a main report.

Duane
Hook'D on Access
MS Access MVP

 

[np3il]

The SQL for "All Shift Avg"
SELECT Sum([Driver-Data].GrossProfit)/16 AS SumOfGrossProfit, Avg([Driver-Data].GrossProfit) AS AvgOfGrossProfit, Sum([Driver-Data].[Shift Time])/16 AS [SumOfShift Time], Avg([Driver-Data].[Shift Time]) AS [AvgOfShift Time], Sum([Driver-Data].Mileage)/16 AS SumOfMileage, Avg([Driver-Data].Mileage) AS AvgOfMileage, Sum([Driver-Data].Stops)/16 AS SumOfStops, Avg([Driver-Data].Stops) AS AvgOfStops, Sum([Driver-Data].[Gallons Delivered])/16 AS [SumOfGallons Delivered], Avg([Driver-Data].[Gallons Delivered]) AS [AvgOfGallons Delivered], Sum([Driver-Data].GrossProfit)/Sum([Driver-Data].[Shift Time]) AS GP_H, Sum([Driver-Data].GrossProfit)/Sum([Driver-Data].Mileage) AS GP_M, Sum([Driver-Data].[Gallons Delivered])/Sum([Driver-Data].[Shift Time]) AS GAL_H, [Driver-Data].[PayRoll Period]
FROM Drivers INNER JOIN [Driver-Data] ON (Drivers.NickName = [Driver-Data].DriverName) AND (Drivers.DriverID = [Driver-Data].DriverID)
GROUP BY [Driver-Data].[PayRoll Period]
HAVING ((([Driver-Data].[PayRoll Period])=[Enter PayRoll Period: ]));


The SQL for "Day Shift Avg"
SELECT Drivers.Shift, Sum([Driver-Data].GrossProfit)/10 AS SumOfGrossProfit, Avg([Driver-Data].GrossProfit) AS AvgOfGrossProfit, Sum([Driver-Data].[Shift Time])/10 AS [SumOfShift Time], Avg([Driver-Data].[Shift Time]) AS [AvgOfShift Time], Sum([Driver-Data].Mileage)/10 AS SumOfMileage, Avg([Driver-Data].Mileage) AS AvgOfMileage, Sum([Driver-Data].Stops)/10 AS SumOfStops, Avg([Driver-Data].Stops) AS AvgOfStops, Sum([Driver-Data].[Gallons Delivered])/10 AS [SumOfGallons Delivered], Avg([Driver-Data].[Gallons Delivered]) AS [AvgOfGallons Delivered], [SumOfGrossProfit]/[SumOfShift Time] AS GP_H, [SumOfGrossProfit]/[SumOfMileage] AS GP_M, [SumOfGallons Delivered]/[SumOfShift Time] AS GAL_H, [Driver-Data].[PayRoll Period]
FROM Drivers INNER JOIN [Driver-Data] ON (Drivers.DriverID = [Driver-Data].DriverID) AND (Drivers.NickName = [Driver-Data].DriverName)
GROUP BY Drivers.Shift, [Driver-Data].[PayRoll Period]
HAVING (((Drivers.Shift)="Day") AND (([Driver-Data].[PayRoll Period])=[Enter PayRoll Period: ]));


The SQL for "Night Shift Avg"
SELECT Drivers.Shift, Sum([Driver-Data].GrossProfit)/6 AS SumOfGrossProfit, Avg([Driver-Data].GrossProfit) AS AvgOfGrossProfit, Sum([Driver-Data].[Shift Time])/6 AS [SumOfShift Time], Avg([Driver-Data].[Shift Time]) AS [AvgOfShift Time], Sum([Driver-Data].Mileage)/6 AS SumOfMileage, Avg([Driver-Data].Mileage) AS AvgOfMileage, Sum([Driver-Data].Stops)/6 AS SumOfStops, Avg([Driver-Data].Stops) AS AvgOfStops, Sum([Driver-Data].[Gallons Delivered])/6 AS [SumOfGallons Delivered], Avg([Driver-Data].[Gallons Delivered]) AS [AvgOfGallons Delivered], [SumOfGrossProfit]/[SumOfShift Time] AS GP_H, [SumOfGrossProfit]/[SumOfMileage] AS GP_M, [SumOfGallons Delivered]/[SumOfShift Time] AS GAL_H, [Driver-Data].[PayRoll Period]
FROM Drivers INNER JOIN [Driver-Data] ON (Drivers.DriverID = [Driver-Data].DriverID) AND (Drivers.NickName = [Driver-Data].DriverName)
GROUP BY Drivers.Shift, [Driver-Data].[PayRoll Period]
HAVING (((Drivers.Shift)="Night") AND (([Driver-Data].[PayRoll Period])=[Enter PayRoll Period: ]));

The others are just a little different but with very minor changes like the division part.

Thanks.

 

[dhookom]

I guess I would use a single query rather than 2 queries for Night and Day. There would be no reason to create 2 then combine them back into 1 with a union query.

Also, IMO, never use parameter prompt queries faq701-6763.

Duane
Hook'D on Access
MS Access MVP

 

[np3il]

Thanks,

I was able to UNION the SQL Queries except that Access is not allowing me to run the third SQL statement in the newly created Query.

I need to combine 9 Queries together in 1 report.

Been testing the UNION of 2 SQL Queries and works great, but when I add the third SQL ... BOOM ... That is all she wrote ...

Can a SubReport work in this case?
Is it the same as creating a report from a query?

Thanks.

 

[lameid]

np3il,

As duane points out you do not need a union for night and day... just remeove the criteria under Driver.Shift in either query and you will get the results for both night and day.

As for your third query, it may help if you posted your SQL.

However in order for Union queries to work, each select statement has to have the same number of fields/columns.


Sub Reports... Basically you create a report as normal and then you drag and drop it into your main report from the database window (at least Through 2003 anyways, I've seen the ribbon but don't know it).

Lastly and I don't think this applies to your situation you would set master and child fields so the sub report shows appropriate recrods compared to the main form.

 

[np3il]

Thanks lameid for the clarifications. But I need the Day Shift Average Totals displayed separate from the Night Shift Average Totals and the Entire Day/Night (All) Shift Average Totals.

If you observe the above SQL statements are the actual SQL I have for the individual queries. I tried the UNION with all three queries. I am getting four lines of totals (2 Day & 2 Night). Individually these queries run good and the numbers are OK, but after the UNION all the numbers do not match with the individual results.

I did add the Shift field to the All Shift SQL query and I get the below result:
GP Avg ...
Day 99999.99 9999.99 ...
Day 99999.99 9999.99 ...
Night 99999.99 9999.99 ...
Night 99999.99 9999.99 ...

With all the numbers mismatched.

Thanks

 

[lameid]

I missed it before you are dividing by different values depending on day or night.... You can group as I suggested but you will have to use IIF to test the shift to get the appropriate value to make it work. You could also make a shift table and store the 10 for day and 6 for night and use it in your query. This is probably best in case the values ever need to change.