Hi
I have a query that shows me some space related figures called qryShelfSpaceAvailability as follows:
Code:
SELECT tblBay.bay, tblBay.bay_width_mm, tblBay.bay_depth_mm, Sum(Nz([bin_width_mm],0)) AS used_space_by_bin_type, [tblBay].[bay_width_mm]-[used_space_by_bin_type] AS shelf_space_available, Nz([SumOfbin_width_mm],0) AS shelf_space_used, Nz([shelf_space_available]-[shelf_Space_used],0) AS shelf_space_free
FROM (tblBay LEFT JOIN qryShelfUsedSpaceByBinType ON tblBay.bay = qryShelfUsedSpaceByBinType.bay) LEFT JOIN qryShelfSpace ON tblBay.bay = qryShelfSpace.bay
WHERE (((tblBay.deactivate)=False))
GROUP BY tblBay.bay, tblBay.bay_width_mm, tblBay.bay_depth_mm, Nz([SumOfbin_width_mm],0);
I want to total these figures and show them on a form/report.
I need various totals based on different criteria so for example I have other queries adding up the various totals I require, here are 3 examples with different WHERE clauses:
Code:
SELECT Sum(qryShelfSpaceAvailabilty.shelf_space_available) AS [Total Shelf Space], Sum(qryShelfSpaceAvailabilty.shelf_space_used) AS [Used Shelf Space], Sum(qryShelfSpaceAvailabilty.shelf_space_free) AS [Free Shelf Space]
FROM qryShelfSpaceAvailabilty;
Code:
SELECT Sum(qryShelfSpaceAvailabilty.shelf_space_available) AS [Total Shelf Space], Sum(qryShelfSpaceAvailabilty.shelf_space_used) AS [Used Shelf Space], Sum(qryShelfSpaceAvailabilty.shelf_space_free) AS [Free Shelf Space]
FROM qryShelfSpaceAvailabilty
WHERE (((qryShelfSpaceAvailabilty.bay_depth_mm)=600));
Code:
SELECT Sum(qryShelfSpaceAvailabilty.shelf_space_available) AS [Total Shelf Space], Sum(qryShelfSpaceAvailabilty.shelf_space_used) AS [Used Shelf Space], Sum(qryShelfSpaceAvailabilty.shelf_space_free) AS [Free Shelf Space]
FROM qryShelfSpaceAvailabilty
WHERE (((qryShelfSpaceAvailabilty.bay_depth_mm)=900));
I have 14 different queries adding up different totals that I require and I need to display the totals on the same form/report. I created an additional query that pulls in all these totals into one query so I can display them together.
This is not efficient and I want to know whether I can calculate all the totals directly in the form/report when qryShelfSpaceAvailability is the control source without creating all these additional queries to sum the totals.