Select columns in query for which other column has min value

[levi84]

Hi, I have a beginners question about queries. I have a table containing 3 fields, product number, provider number, and price. For each product number there are several providers, with different prices. I want to make a query that contains the minimum price and the provider for each product. I know how to get the mininum price per product (just min of price and group by product number), but how do i get an extra column that gives me the number of the provider corresponding with the minimum price for this product? Thank you for your help!

[alansidman]

I did this with two queries. Someone else may be able to offer a solution with one.

SELECT Table1.ProdNr, Min(Table1.Price) AS MinOfPrice
FROM Table1
GROUP BY Table1.ProdNr;

SELECT Query1.ProdNr, Query1.MinOfPrice, Table1.ProvNr
FROM Query1 INNER JOIN Table1 ON (Query1.MinOfPrice = Table1.Price) AND (Query1.ProdNr = Table1.ProdNr);