Select lowest number from multiple rows in SQL

[cs8404]

I cannot quite figure out how to accomplish the following results.
My table is "Products" with the following fields:
ID Item Metal Size Price
1 Ring 18ctGold 4-7 $23.00
67 Ring 18ctGold 8-11 $30.00
70 Ring Silver 4-7 $10.00
75 Ring Silver 8-11 $15.00
I simply want to be able to show unique metal AND lowest price for each
metal; so, for above I would want to show rows with ID 1 and 70 only.
SELECT DISTINCT comes close but returns all distinct prices.

Would something like this call for sub-queries?
Any help from experts out there to figure this one is most appreciated.

[Allen Browne]

1. Create a query into this table.

2. Depress the Total button on the toobar (upper sigma icon.)
Access adds a Total row to the design grid.

3. Drag the Metal field into the grid.
Accept "Group By" in the Total row.

4. Drag the Price field into the grid.
In the Total row under this field, choose Min.

That's it.

If you actually wanted to bring in other fields as well, this article
explains 4 approaches:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<cs****@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

I cannot quite figure out how to accomplish the following results.
My table is "Products" with the following fields:
ID Item Metal Size Price
1 Ring 18ctGold 4-7 $23.00
67 Ring 18ctGold 8-11 $30.00
70 Ring Silver 4-7 $10.00
75 Ring Silver 8-11 $15.00
I simply want to be able to show unique metal AND lowest price for each
metal; so, for above I would want to show rows with ID 1 and 70 only.
SELECT DISTINCT comes close but returns all distinct prices.

Would something like this call for sub-queries?
Any help from experts out there to figure this one is most appreciated.

[cs8404]

Many thanks, Allen - my query is now finally working the way that I
needed it to!