+ Reply to Thread
Results 1 to 8 of 8

SQL Query to sum distinct values and perform a join

  1. #1
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    2007
    Posts
    128

    SQL Query to sum distinct values and perform a join

    Hi,
    I have a database showing quantities delivered by suppliers.

    The main table, tblMain has the fields Supplier, Date, Quantity
    The Supplier field is an ID from a table of suppliers.

    What I need to do is produce an ADO query which retrieves a unique list of Supplier ID's between two dates with a sum of the quantity.
    I then need to join that to the table of suppliers to obtain the supplier name from the supplier ID.

    This is the code I have so far to just get the supplier and the sum. But the join isn't working and I haven't attempted to tackle the date range yet.

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,582

    Re: SQL Query to sum distinct values and perform a join

    Maybe:

    PHP Code: 
    SELECT tblSupplierList.SuppliertblMain.DateSum(tblMain.Quantity) AS SumOfQuantity
    FROM tblMain INNER JOIN tblSupplierList ON tblMain
    .Supplier tblSupplierList.fID
    GROUP BY tblSupplierList
    .SuppliertblMain.Date
    HAVING 
    (((tblMain.DateBetween #1/1/2020# And #1/31/2020#)); 

  3. #3
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    2007
    Posts
    128

    Re: SQL Query to sum distinct values and perform a join

    Hi There,
    Many thanks for your reply.

    It's pulling out the supplier names but there is still an issue with the summing.
    So if you had the following in the table:

    Date , Supplier, Quantity
    01/01/2020, MySupplier, 1
    01/01/2020, MySupplier, 1
    05/01/2020, MySupplier, 2
    05/01/2020, MySupplier, 2
    05/01/2020, MySupplier, 2

    What results in the recordset for me is a separate entry for the sum of each quantity per date/Supplier:
    MySupplier, 01/01/2020, 2
    MySupplier, 05/01/2020, 6

    When what I'm hoping for is just the Supplier and the sum of the quantity between the dates.
    My Supplier, 8

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,582

    Re: SQL Query to sum distinct values and perform a join

    Attached is my mock up of your example.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    2007
    Posts
    128

    Re: SQL Query to sum distinct values and perform a join

    Hi,
    I had a look at the file. It seems to return
    MySupplier 01/01/2020, 2
    MySupplier 05/01/2020, 6

    Is it possible to return
    MySupplier, 8

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,582

    Re: SQL Query to sum distinct values and perform a join

    With the query open click on the Sigma as shown in the attached. Select Sum.

    Alternatively, export the query to excel and create a Pivot table as also shown
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by alansidman; 02-13-2020 at 09:02 AM.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019
    Posts
    16,582

    Re: SQL Query to sum distinct values and perform a join

    Here is one more option. This will total by supplier.

    PHP Code: 
    SELECT tblSupplierList.SupplierSum(tblMain.Quantity) AS SumOfQuantity
    FROM tblMain INNER JOIN tblSupplierList ON tblMain
    .Supplier tblSupplierList.fID
    WHERE 
    (((tblMain.DateBetween #1/1/2020# And #1/31/2020#))
    GROUP BY tblSupplierList.Supplier

  8. #8
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    2007
    Posts
    128

    Re: SQL Query to sum distinct values and perform a join

    Thank you so much. That works exactly how I'd hoped. Really appreciate your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (0 members and 2 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1