Finding lowest value

[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

 

[John Spencer]

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

 

[KARL DEWEY]

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];

 

[John Spencer]

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 DEWEY]

You are correct. I need to take the time to build the tables and test.
--
Build a little, test a little.

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 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];

.

 

[jaworski_m]

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.