Results 1 to 3 of 3
  1. #1
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86

    An efficient way of showing totals from a query

    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.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Looks like need to do one query with GROUP BY clause. Or build report with raw data and use its Sorting & Grouping feature with aggregate calc in textbox.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    anthgav is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Posts
    86
    Quote Originally Posted by June7 View Post
    Looks like need to do one query with GROUP BY clause. Or build report with raw data and use its Sorting & Grouping feature with aggregate calc in textbox.
    Thanks June. I managed to find a solution based on the advice you gave me. I added text boxes into the report footer to calculate the totals and hid the Detail section of the report. This gives me what I want and also makes my report much faster compared to how I set it up previously. I realised my old method wasn't very efficient and I've changed other reports based on this method and everything is much slicker now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Built In Export for Query to Excel Not Showing Totals
    By PinkDuster in forum Import/Export Data
    Replies: 2
    Last Post: 02-21-2018, 07:48 AM
  2. Replies: 3
    Last Post: 08-04-2017, 01:28 PM
  3. Query showing totals
    By trident in forum Queries
    Replies: 4
    Last Post: 12-16-2014, 03:19 PM
  4. Report totals not showing
    By zburns in forum Reports
    Replies: 6
    Last Post: 10-08-2014, 07:23 AM
  5. Replies: 7
    Last Post: 12-07-2009, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums