Group Min and Max

[Vina]

CAn someone enlighten me on how I can accomplish this.
I have a query that has a running total. What I need to
do is only display 1 record for each item.

Item 1 100 1/1/03
Item 1 -10 1/2/03
Item 1 -20 1/3/03
Item 2 200 1/1/03
Item 2 100 1/2/03
Item 2 -30 1/3/03
Item 2 -40 1/4/03
Item 3 300 1/1/03

What I need is only take
Item 1 -10 1/2/03
Item 2 -30 1/3/03
Item 3 300 1/1/03

I had tried grouping and take the min or max qty but if I
take the min or max how can I correspond it to the right
date, I cannot use the min or max for the date. Can
someone help me on how should I handle this?

 

[John Spencer (MVP)]

One way is to use a subquery in the main query.

SELECT ItemName, FldValue, FldDate
FROM SomeTable
WHERE FldValue In
(SELECT Min(T.FldValue)
FROM SomeTable AS T
WHERE T.ItemName = SomeTable.ItemName)