Return Minimum Value within Group with ID

[LTRService]

How do I create a query to return just one record, a minimum value for one of the fields, per group within a group that contains an ID field?

This is what my query is returning:
ID, UPC, MinOfAverage Unit Cost
19161, 026851003663, $1.15
15927, 026851003663, $1.11
4459, 026851003663, $1.18
19598, 029695215513, $39.73
15993, 029695215513, $39.29
11694, 029695215513, $48.81

This is what I need:
ID, UPC, MinOfAverage Unit Cost
15927, 026851003663, $1.11
15993, 029695215513, $39.29

I would be *extremely* grateful for an answer. I am new to this list, but I have plenty it MS Access help karma to my name, so I'm hoping to cash some of that that in.

Fingers crossed.

-Tom

 

[David Eagar]

Group your query by Min will give you the result you want

 

[ajetrumpet]

How do I create a query to return just one record, a minimum value for one of the fields

Like David said, GROUP it...

SELECT..., Min("[unit cost]", "table")
FROM()
GROUP BY [UPC];

 

[Jon K]

You can do it in two steps.

First, build a Totals Query from your table, Group By UPC and get the Min value of [MinOfAverage Unit Cost].

qryOne:-
SELECT UPC, Min([MinOfAverage Unit Cost]) AS MinCost
FROM [TableName]
GROUP BY UPC;


Then join the query back to the table in a second query to retrieve the records from the table.

qryTwo:-
SELECT [TableName].*
FROM [TableName] INNER JOIN qryOne ON ([TableName].[MinOfAverage Unit Cost] = qryOne.MinCost) AND ([TableName].UPC = qryOne.UPC);


Run the second query. That should do the trick.
.