+ 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
    365 32bit
    Posts
    168

    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
    18,260

    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#)); 
    Last edited by alansidman; 02-12-2020 at 04:32 AM.

  3. #3
    Forum Contributor
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit
    Posts
    168

    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
    18,260

    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
    365 32bit
    Posts
    168

    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
    18,260

    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
    18,260

    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
    365 32bit
    Posts
    168

    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 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] get distinct values from criteria AND sum all of the values of the distinct
    By dluhut in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-10-2017, 02:57 PM
  2. [SOLVED] Null values get matched in Full Outer Join query. Can we stop this?
    By XLn3wb in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 10-26-2016, 04:32 AM
  3. [SOLVED] Loop through list, perform web query and save each query on its own page
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-07-2012, 07:50 AM
  4. inner join query
    By masond3 in forum Excel General
    Replies: 0
    Last Post: 09-05-2012, 06:46 AM
  5. Query Join / Union
    By ciprian in forum Access Tables & Databases
    Replies: 26
    Last Post: 09-14-2011, 01:36 PM
  6. Count Distinct Query
    By jello1000 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-22-2007, 07:28 PM
  7. USE OF DISTINCT COMMAND IN MS QUERY
    By Prakash in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-24-2006, 07:10 PM

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