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 in advance if my dialogue isn't clear.

Thanks for your help and direction.

Assuming you are desirous of creating an encounter (report) and having
designed an encounter application, I suggest the following.

Why use a table at all? I suggest a query using your Patient table and your
test results table. Your (encounter form?) could encapsulate 1 encounter for
each patient (regardless of the # of tests) per unique date & unique time.
Set query properties to unique VALUES (not unique RECORDS) using date, time,
& Pat ID fields in the test results table and appropriate fields from your
patients table to capture your patient demographics.

If you must (duplicate) this information into a table for other reasons
(you've already captured all the data you need in your test results table),
create the above select query and change it to an append or make-table query
to append or create your data table.

hth

Judi B

"Dale" wrote:

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 in advance if my dialogue isn't clear.

Thanks for your help and direction.

=?Utf-8?B?SnVkaSBC?= wrote in
:

Why use a table at all?

Because his current scheme is badly denormalised and he wants to fix it...


Tim F