Access and duplicates

[Kemi]

Hello all,

I have a table with 3 fields, namely: ID, Bill Date and Transaction
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 and
the earliest transaction date for any record that is duplicated. If a
record is unique, i.e. it is not duplicated, I want it to return that
record as well. In the example, ID 1 is duplicated 3 times, but I
only want it to return 1 record with ID 1, Bill Date: 12/01/2004 and
Transaction date: 15/01/2004. ID 2 is duplicated 2ce, I want it to
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 record
as is.

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


Kemi

 

[Les]

Hi,
I think you just need to create a totals query, group by
ID, group by Bill Date and select Min transaction date.
You can change the query to a make-table query.

 

[Jeff Boyce]

Kemi

Open a query, select the fields. Open the Properties list for the query,
select Unique Values, and set it to Yes. No need to "build" another table
(unless you are going to replace the one with the duplicates).