values of each product and your suppliers?

  • Thread starter Thread starter Frank Dulk
  • Start date Start date
F

Frank Dulk

How do I do in a query to catch the smallest values of each product and your
suppliers?

I get to recover the product the smallest value it just lacks the suppliers,
because when I increase the suppliers it duplicates the records
 
Frank, I assume you get some products from multiple suppliers.
You therefore have 3 tables, something like this:

tblProduct
ProductID AutoNumber primary key
ProductName...

tblSupplier:
SupplierID AutoNumber primary key
SupplierName...

tblProductSupplier:
ProductID Number matches tblProduct.ProductID
SupplierID Number matches tblSupplier.SupplierID
PriceEach Currency Cost of this product from this supplier.

Now you want to know the lowest price from each Product. You could use a
subquery to find either the supplier or the price, but not both at once.
This query shows how to get the cheapest supplier for the product:

SELECT tblProduct.*,
( SELECT TOP 1 SupplierID
FROM tblProductSupplier
WHERE tblProductSupplier.ProductID = tblProduct.ProductID
ORDER BY PriceEach, tblProductSupplier.SupplierID )
AS CheapestSupplier
FROM tblProduct;

For more help, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
Michel Walsh explains 4 ways to get the result.
 
Thank you.
A detail, I am inspired by you.


Allen Browne said:
Frank, I assume you get some products from multiple suppliers.
You therefore have 3 tables, something like this:

tblProduct
ProductID AutoNumber primary key
ProductName...

tblSupplier:
SupplierID AutoNumber primary key
SupplierName...

tblProductSupplier:
ProductID Number matches tblProduct.ProductID
SupplierID Number matches tblSupplier.SupplierID
PriceEach Currency Cost of this product from this supplier.

Now you want to know the lowest price from each Product. You could use a
subquery to find either the supplier or the price, but not both at once.
This query shows how to get the cheapest supplier for the product:

SELECT tblProduct.*,
( SELECT TOP 1 SupplierID
FROM tblProductSupplier
WHERE tblProductSupplier.ProductID = tblProduct.ProductID
ORDER BY PriceEach, tblProductSupplier.SupplierID )
AS CheapestSupplier
FROM tblProduct;

For more help, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
Michel Walsh explains 4 ways to get the result.
Click to expand...
 
Back
Top