Hi, I'm trying to use a query to tell me which Shelf Locations are holding the most stock of 4 items. There are 2 tables, one with the products and another with the locations, which have products in them and also shows the qty of each product. Some products are in multiple locations but I only want to show the location that has the most stock in it. Grateful if anyone can show me where I'm going wrong.

I've attached a small test file to illustrate my problems. I was expecting the query to produce the following results:

Most stock of product 1198 in Bay 3

Most of 1201 in Bay 3

Most of 5626 in Bay B5

Most of 5628 in Bay C6N

thanks.

Try Creating the following Two Queries:

1. Query1

SELECT Tbl_TestLocations.Location, Tbl_TestLocations.Product, Max(Tbl_TestLocations.[Qty of Product]) AS [MaxOfQty of Product]
FROM Tbl_TestLocations
GROUP BY Tbl_TestLocations.Location, Tbl_TestLocations.Product
HAVING (((Max(Tbl_TestLocations.[Qty of Product]))>0));

2. Query2

SELECT Tbl_TestPackage.Product, Tbl_TestPackage.[Select], Query1.Location, Query1.[MaxOfQty of Product]
FROM Tbl_TestPackage INNER JOIN Query1 ON Tbl_TestPackage.Product = Query1.Product;

Many thanks for your time. Unfortunately I only want one occurence for each item. As item 1321198 has 55 in Bay 3 and 50 in Bay 5 both entries are shown. I only want a single entry for the location with the most stock of each item.

Regards

Chris