Finding lowest value

  • Thread starter Thread starter jaworski_m
  • Start date Start date
J

jaworski_m

There are the following tables (1-many ralation)
tbl_Distibutors
distributor_name (PK)
(other fields)

tbl_Products
product_name
product_price
(other fields)

I would like to build a query to get the following results:
Distributor_1, product A, (lowest price for product A)
Distributor_5, product B, (lowest price for product B)
and so on...

I would appreciate any suggestion.

win xp
access 2003
 
Impossible to say unless you tell us how to link a distributor to a product.
For instance do you have a field in tbl_products that has the distributor name
or do you have a junction table tbl_DistributorProducts that links a product
to a distributor.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
You say the two table have a one-to-many relationship but the table
tbl_Products does not have a foreign key that relates to distributor_name.

If you did then this would work --
SELECT [distributor_name], [product_name], Min([product_price]) AS Price
FROM tbl_Distibutors INNER JOIN tbl_Products ON
[tbl_Distibutors].[distributor_name] = [tbl_Products][distributor_name]
GROUP BY [distributor_name], [product_name];
 
Karl,
It seems to me that your solution would return the minimum price but it would
return the same minimum price for every distributer that offered the product.

To get the information the relevant record(s) from tbl_Distributers and
tbl_Products you would need something more complicated like the following
UNTESTED query.

SELECT Tbl_Distributers.Distributer_Name
, Tbl_Products.Product_Name
, tbl_Products.Product_Price
FROM (tbl_Distributers INNER JOIN Tbl_Products
ON tbl_Distributers.Distributer_Name = Tbl_Products.Distributer_Name)
INNER JOIN
(SELECT Product_Name, Min(Product_Price) as LowPrice
FROM Tbl_Products
GROUP BY Product_Name) as QLow
ON Tbl_Products.Product_Name = QLow.Product_Name

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
You are correct. I need to take the time to build the tables and test.
--
Build a little, test a little.


John Spencer said:
Karl,
It seems to me that your solution would return the minimum price but it would
return the same minimum price for every distributer that offered the product.

To get the information the relevant record(s) from tbl_Distributers and
tbl_Products you would need something more complicated like the following
UNTESTED query.

SELECT Tbl_Distributers.Distributer_Name
, Tbl_Products.Product_Name
, tbl_Products.Product_Price
FROM (tbl_Distributers INNER JOIN Tbl_Products
ON tbl_Distributers.Distributer_Name = Tbl_Products.Distributer_Name)
INNER JOIN
(SELECT Product_Name, Min(Product_Price) as LowPrice
FROM Tbl_Products
GROUP BY Product_Name) as QLow
ON Tbl_Products.Product_Name = QLow.Product_Name

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

KARL said:
You say the two table have a one-to-many relationship but the table
tbl_Products does not have a foreign key that relates to distributor_name.

If you did then this would work --
SELECT [distributor_name], [product_name], Min([product_price]) AS Price
FROM tbl_Distibutors INNER JOIN tbl_Products ON
[tbl_Distibutors].[distributor_name] = [tbl_Products][distributor_name]
GROUP BY [distributor_name], [product_name];
Click to expand...
.
Click to expand...
 
Thank you for your answers.
Indeed there's a juction table.

Relationships:

tbl_Distributors
distributor_name (PK)
(other fields)

tbl_DistributorProducts (junction table)
distributor_name (PK)
product_id (PK)
component_price
(other fields)

tbl_Products
product_id (PK)
product_name
(other fields)

Thank you for suggestions.
 
Back
Top