First Record Function

[KAT1162]

Hello,
I thought this would be easier, but the results I'm getting are inaccurate.

I have a table that includes items and dates. There are multiple records per item. I want to show each item only once with the earliest corresponding date. I've sorted, used the TOP, DISTINCT, and FIRST functions and can not seem to come up with the right combination.

The query is already works from another query that sorts by item and date. I just need to pull the first record for each. I would appreciate any suggestions!

 

[pbaldy]

Try Min rather than First.

 

[KAT1162]

Thanks. Actually I have one more column in there for quantity. I used the MIN in the date field and FIRST in the quantity field (MIN didn't work), and it looks like it's working. Thanks for your help!

 

[pbaldy]

I'd be careful with First, as it often doesn't do what you expect:

http://support.microsoft.com/kb/208190/en-us

One way to get a value associated with the record with the Min date is a two step process. First is a query that groups by item and gets the min date. A second query joins the table and that first query on those two fields, enabling you to pull other data from that record.