duplicates and access

O

opeyemi1

Hello all,

I have a table with 3 fields, namely: ID, Bill Date and Transactio
date. Some of the records in this table are duplicated with the ID
Bill Date and Transaction date reoccuring, e.g.

ID Bill Date Transaction date
1 12/01/2004 15/01/2004
1 12/01/2004 15/01/2004
1 12/01/2004 17/01/2004
2 12/01/2004 16/01/2004
2 12/01/2004 18/01/2004
3 12/01/2004 16/01/2004

I want to create another table that will return the ID, Bill Date an
the earliest transaction date for any record that is duplicated. If
record is unique, i.e. it is not duplicated, I want it to return tha
record as well. In the example, ID 1 is duplicated 3 times, but I onl
want it to return 1 record with ID 1, Bill Date: 12/01/2004 an
Transaction date: 15/01/2004. ID 2 is duplicated 2ce, I want it t
return 1 record, ID 2, Bill Date 12/01/2004, Transaction date
16/01/2004. Since ID 3 is not duplicated, it should return the recor
as is.

I hope someone can figure this out for me. Thank you very much i
advance.


Kem
 
Top