Unique table value: distinctrow

I'll preface by saying I'm not sure where to best post this, queries or
tabledesign. For a study that is being conducted, I have a test table that
holds 4 million records, patient demograpic information with "test result",
one record for each "test result" for each date and time the test was
performed. For every "test result" for the same date and time the patient
demographic information is duplicated.

Each patient has a unique identifier. I am wanting to build an encounters
table having a unique record for each encounter date and time a time a test
was done. If 10 tests were ordered for one date and time I only want to
capture one instance of the visit. My encounters table is PatId, Date,
Time, Location where PatId, Date and Time are primary keys.

I had to do a "double filter" of the 4 million records to get the end result
of a table of encounters w/o duplicates. I could not append directly into
the encounters from the test table due to key violations...which was the my
rationale for doing the append in the first place. I had to do a 2-step
process using a make table query based on a distinctrow clause, then append
into the encounters table from table created in this intermediate step.

Why didn't an append directly into the encounters table from the test table
work? Was it because there were just too many records for access to manage?
Thus my workaround?

I'm working in Access 2000 as I need the 2G capacity, the database is 1.2G
at this moment.

I appologize if this isn't clear.

"Dale" wrote in
:

Each patient has a unique identifier. I am wanting to build an
encounters table having a unique record for each encounter date and
time a time a test was done. If 10 tests were ordered for one date
and time I only want to capture one instance of the visit. My
encounters table is PatId, Date, Time, Location where PatId, Date and
Time are primary keys.

INSERT INTO Visits (PtID, VisitDate, VisitTime, Location)
SELECT DISTINCT PatID, TestDate, TestTime, Location
FROM Tests


Note that this will fail if you have records that have different Locations
for the same (PatID, Date, Time) so you may have some cleaning to do first.
That's normal theory for you! I would suggest inserting the records into a
temp table first in order to check and clean.

Oh, and don't forget the usual copious backups...


Hope that helps


Tim F