duplicates and access

[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

 

[MacDermott]

Since in your example all of the Bill Dates are the same, it's a little hard
to tell whether the Bill Date is always the same for a given ID, or if it's
not, whether you want the first transaction date for each bill date, or the
first transaction date for each ID. (BTW if the bill date is always the
same for an ID, it would be better to create 2 tables).

In either case, you can look at using a Totals Query with the Min function.

HTH
- Turtle