SUM query (only include values if they meet a condition)

[gmworrall]

I have many suppliers for many products. I want to get a total quantity for each product (stock from all suppliers). This bit is pretty simple!

The problem is, two of the suppliers are actually the same. In the below example, it's A and B. There isn't duplication for every product, they sell different items but occasionally cross over. When they DO cross over, I don't want to count that stock twice.

I don't need to keep any specific supplier info for this exercise. So removing supplier B where supplier A already exists for that product would work. I just can't figure out how to do it! A query? Programmatically?

supplier productID quantity
A 1 92
B 1 92
C 1 10
D 2 2
B 2 16

Required output
productID SumOfQuantity
1 102
2 18

 

[jzwp22]

The problem is, two of the suppliers are actually the same. In the below example, it's A and B

Supplier A & B are the same supplier? Why would you have two different identifications for the same supplier?

Do you have a tables that associates supplier A with supplier B?

 

[gmworrall]

Supplier A & B are the same supplier? Why would you have two different identifications for the same supplier?

Do you have a tables that associates supplier A with supplier B?

They operate separately. We have different contacts and get a different feed from each. For everything apart from this part, it works for us to keep them separate.

It might be better to think of them as different suppliers who share stock.

 

[jzwp22]

So in essence, they are related suppliers, so I think you need a table to capture that relationship

tblRelatedSuppliers
-pkRelateSupplierID primary key, autonumber
-fkPSupplierID foreign key to tblSuppliers (primary supplier A in your case)
-fkSSupplierID foreign key to tblSuppliers (secondary supplier B in your case)

What happens if a cross-over occurs for a particular product but the quantities are different? Which record should be included in the count?

 

[gmworrall]

So in essence, they are related suppliers, so I think you need a table to capture that relationship

tblRelatedSuppliers
-pkRelateSupplierID primary key, autonumber
-fkPSupplierID foreign key to tblSuppliers (primary supplier A in your case)
-fkSSupplierID foreign key to tblSuppliers (secondary supplier B in your case)

What happens if a cross-over occurs for a particular product but the quantities are different? Which record should be included in the count?

I'll get that table set up!

Quantities are very likely to always be the same - I think they share a database. However, in that instance I'd take the higher value.

 

[jzwp22]

In the attached database, I illustrate how I would use the new table to get to the summary you want. The final summation by product is shown in the query: qrySumByProduct

The first query (qryProductQtyByPrimarySupplier) uses a Dlookup() function to get the primary supplier that is related to a secondary supplier if there is such a relationship defined in the new table. I then essentially use that result to assign the secondary supplier's quantity to the primary supplier via another calculated field.


Then I created another query (qryGetQtyMaxForEachProdByPrimarySupplier) that finds the max grouped by calculated field of previous query.

I then use that max query above in qrySumByProduct to do the final summation.

 

[gmworrall]

SPOT ON!

Easy to follow example and a great solution. Thankyou.

 

[jzwp22]

You're welcome