+ Reply to Thread
Results 1 to 8 of 8

SQL Query to sum distinct values and perform a join

  1. #1
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,811

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,811

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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 Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,811

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    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 Expert
    Join Date
    07-23-2018
    Location
    UK
    MS-Off Ver
    365 32bit Win
    Posts
    1,811

    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