Seperating duplicating records

If I understand what you want, here is a procedure to make a new table
of records with unique PatientIDs using their earliest date.

1. First make a backup of the table.

2. Copy the original table (Table1)'s structure (structure only) to a new
table (Table2).

3. Set the Patient ID in this table as a primary key (in Design View,
Edit-Primary Key).

4. Go back and sort the original table (Table1) on the date field (click on
Date column header, choose ascending).

5. Copy all the records from this table (Table1) into Table2.
(highlight upper left corner of Table1, Ctl-C, minimize Table1, Open
Table2,highlight upper left corner, Ctl-V) .

6. As the copy progresses you should get a message that it could not copy
all records because of key viloations. Answer the following prompts as
indicated below.

The Changes you requested ... : Click Ok
Do you want to supress ...: Click Yes
You are about to paste ... : Click Yes
Records that Microsoft Access were unable ...: Click OK/


This will prevent copying records with duplicate Patient ID fields.

Your result (Table2) should be a table of records with unique Patient IDs,
sorted by date.


--
---------------------------------------------------------------
Michael J. Strickland
Quality Services
703-560-7380
---------------------------------------------------------------
"Ally Chang" wrote in message
...
Hi,

I have a table, for example, of 7 unique patients with each patient having
multiple lines. Although ID is the same in each line, there are some other
information in about 15 fields in each line. (The ID and Date fields are
two of 15 fields in each line). For example, th ID 000012289 has three lines
with the Date field first starting on Jan 18th, 2004 (first line), then in
Feb 2004 (second line) and March 2004 (the third line). Another ID 000020158
has four lines with the Date filed first starting on Dec 12, 2003, etc.. My
goal is to separate the ID based on its very first date into groups while
still keeping the the rest of lines with each patient. In oter words, I need
to group the ID only based on the very first value of the Date field. Are
there anyone having any idea of how to solve the problem?

Thanks very much for your time! Waiting...