Min Grouping query question

[topdog]

I'm listing grocery items in a table. How can I return the Min priced
ITEM Grouping by ITEM (if there are ties, return all ties). There are
duplicate values for each group of ITEMS (Paper Plates with Price $1,
$2, $3).

I need to return ALL the fields listed below.
Thanks a zillion.

SELECT tblItems.Item, tblItems.Price, tblItems.Category,
tblItems.Store, tblItems.Units, tblItems.Description
FROM tblItems

 

[Jeff Boyce]

One approach would be to find the minimum price in a first query, then use
that query (with price and item) as a source in a second query, joined back
to the main table to pick up the rest of the items.

 

[topdog]

Thanks but isn't there a way to do it all in one query?
Thanks again !

 

[John Spencer (MVP)]

Pardon me for jumping in.

Yes, it can be done in one query. but it involves using a correlated subquery or
using a subquery as a table source.

Here are two untested variations

SELECT tblItems.Item, tblItems.Price, tblItems.Category,
tblItems.Store, tblItems.Units, tblItems.Description
FROM tblItems
WHERE tblItems.Price =
(SELECT Min(A.Price)
FROM tblItems as A
WHERE A.Item = tblItems.Item)


OR another variation

SELECT tblItems.Item, tblItems.Price, tblItems.Category,
tblItems.Store, tblItems.Units, tblItems.Description
FROM tblItems INNER JOIN
[SELECT A.Items, Min(A.Price) as MinPrice
FROM tblItems as A
GROUP BY A.Items]. AS B
On tblItems.Item = B.Item AND
tblItems.Price = B.MinPrice

Note that the subquery above is surrounded by square brackets and has a period
at the end. ALSO, you cannot have any square brackets within the subquery or
Access will error.

 

[Jeff Boyce]

As John points out, it is possible to do if you care to work on the SQL
statement level. If you want to do this using the Access Query design mode,
use two queries!