Results 1 to 6 of 6
  1. #1
    steven7 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2016
    Posts
    13

    Total Sum


    Hi,

    I want to run this data and show only the load Number and the total of Quantity ordered. I have displayed only Load Number and Quatity ordered and set the group by to SUm on quantity ordered, but I don't get an accurate figure. Any advice?

    SKU Load Number Quantity Ordered Instem Despatch Date
    B709061/00 266857 330.000 01/10/2016
    B731006/00 266857 130.000 01/10/2016
    BA09010 266857 110.000 01/10/2016
    BA09011 266857 110.000 01/10/2016
    BA31001/00 266857 1.000 01/10/2016
    BA31001/00 266857 279.000 01/10/2016
    BC09020 266857 1.000 01/10/2016
    BC09020 266857 99.000 01/10/2016
    BC19000 266857 120.000 01/10/2016
    BC19000 266857 240.000 01/10/2016
    BC30001 266857 34.000 01/10/2016
    BC31001 266857 186.000 01/10/2016
    BC31001 266857 1,014.000 01/10/2016
    FX09019 266857 2.000 01/10/2016
    FX09019 266857 73.000 01/10/2016

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what does 'I don't get an accurate figure' mean?

  3. #3
    steven7 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2016
    Posts
    13
    I want to get a total figure from all the quantity ordered totals. When I run the query as described, the total is incorrect. In this particular instance, it is exactly 10 times the total.

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    so your total is 27290 rather than 2729.

    This implies there is something wrong with your query

    What is the sql to your query?

  5. #5
    steven7 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    May 2016
    Posts
    13
    Quote Originally Posted by Ajax View Post
    so your total is 27290 rather than 2729.

    This implies there is something wrong with your query

    What is the sql to your query?


    SELECT [Pad Wk1].[Load Number], Sum(completed.[Quantity Ordered]) AS [SumOfQuantity Ordered]
    FROM [Pad Wk1] LEFT JOIN completed ON [Pad Wk1].[Load Number] = completed.[Load Number]
    GROUP BY [Pad Wk1].[Load Number];

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it's because you have introduced another table which has multiple records with the same loadnumber

    try

    Code:
    SELECT [Load Number], Sum([Quantity Ordered]) AS [SumOfQuantity Ordered]
     FROM completed
     GROUP BY [Load Number]
    and if you want to pick up load numbers without any quantities ordered

    Code:
    SELECT DISTINCT P.[Load Number], Q.[SumOfQuantity Ordered]
    FROM [Pad Wk1] P 
        LEFT JOIN 
            (SELECT [Load Number], Sum([Quantity Ordered]) AS [SumOfQuantity Ordered]
            FROM completed
            GROUP BY [Load Number]) Q
        ON P.[Load Number]=Q.[Load Number]

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

Similar Threads

  1. Sub total and total on invoice form
    By knh2r in forum Access
    Replies: 3
    Last Post: 07-13-2016, 02:09 PM
  2. Total Row
    By caniread in forum Queries
    Replies: 3
    Last Post: 05-25-2016, 05:02 AM
  3. Replies: 3
    Last Post: 01-18-2015, 06:05 PM
  4. Replies: 4
    Last Post: 10-10-2014, 02:39 PM
  5. Total's Box
    By Bike in forum Forms
    Replies: 1
    Last Post: 03-31-2011, 12:15 PM

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