#  Other Applications & Softwares  > Access Tables & Databases >  > [SOLVED] SQL Query to sum distinct values and perform a join

## ByteMarks

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.
```

----------


## alansidman

Maybe:




```

SELECT tblSupplierList.Supplier, tblMain.Date, Sum(tblMain.Quantity) AS SumOfQuantity
FROM tblMain INNER JOIN tblSupplierList ON tblMain.Supplier = tblSupplierList.fID
GROUP BY tblSupplierList.Supplier, tblMain.Date
HAVING (((tblMain.Date) Between #1/1/2020# And #1/31/2020#)); 


```

----------


## ByteMarks

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

----------


## alansidman

Attached is my mock up of your example.

----------


## ByteMarks

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

----------


## alansidman

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

----------


## alansidman

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




```

SELECT tblSupplierList.Supplier, Sum(tblMain.Quantity) AS SumOfQuantity
FROM tblMain INNER JOIN tblSupplierList ON tblMain.Supplier = tblSupplierList.fID
WHERE (((tblMain.Date) Between #1/1/2020# And #1/31/2020#))
GROUP BY tblSupplierList.Supplier; 


```

----------


## ByteMarks

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

----------

