I mocked up a table with some sample data. You'll have to adjust table and field names.
id |
ProdCode |
SupplierName |
Price |
1 |
abc |
Jones |
$230.00 |
2 |
abc |
Smith |
$238.45 |
3 |
xyz |
Jones |
$20.00 |
4 |
xyz |
Smith |
$30.00 |
5 |
acdb |
Jones |
$130.00 |
6 |
acdb |
Smith |
$70.00 |
Here's a first cut at a query to get suppliername, prodcode and min(Price) in ProdCode order.
Code:
Select suppliername
, Prodcode
, price from TestProdsTbl
where price In
(
SELECT Min(TestProdsTbl.Price) AS MinOfPrice
FROM TestProdsTbl
GROUP BY TestProdsTbl.ProdCode)
order by prodcode;
Result:
suppliername |
Prodcode |
price |
Jones |
abc |
$230.00 |
Smith |
acdb |
$70.00 |
Jones |
xyz |
$20.00 |