Sort out earliest date

[vitality]

Hi Everyone,

Does anyone know to sort the earliest date (single result) from different
rows? Sample as below.

Fields: “Invoice Number†& “Invoice Dateâ€; Invoice Number = not unique & can
be repeated.

<DATA>
Invoice Number Invoice Date
A12345 27/03/2008
A12345 29/03/2008
A12345 10/05/2008
A56155 22/05/2008
A56155 25/05/2008
A56155 28/05/2008
A598523 06/06/2008
A598523 15/06/2008
A598523 16/06/2008

Expected result - Keep the earliest Invoice Date only.

<AFTER SORT – RESULT>
Invoice Number Invoice Date
A12345 27/03/2008
A56155 22/05/2008
A598523 06/06/2008

I am not good at Access query and programming, appreciate if you may explain
it in details. Tks.

 

[Allen Browne]

If you don't need other fields, you can do this with a totals query:

1. Depress the Total button on the toolbar in query design.
Access adds a Total row to the design grid.

2. In the Total row under [Invoice Number], accept:
Group By

3. In the Total row under [Invoice Date], choose:
Min

If you do need other fields as well, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

More information about subqueries:
http://allenbrowne.com/subquery-01.html

 

[vitality via AccessMonster.com]

Many thanks, it's work!

If you don't need other fields, you can do this with a totals query:

1. Depress the Total button on the toolbar in query design.
Access adds a Total row to the design grid.

2. In the Total row under [Invoice Number], accept:
Group By

3. In the Total row under [Invoice Date], choose:
Min

If you do need other fields as well, see:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

More information about subqueries:
http://allenbrowne.com/subquery-01.html

Hi Everyone,

[quoted text clipped - 28 lines]

explain
it in details. Tks.