Primary Keys in imported data

[Jeff C]

I am building a database to be used for healthcare data. The Data
comes from imported txt files and combines information from several
reports through queries. I am working on configuring the new
information to be appended into a Master Table each time. This will
require a primary key which I do not have yet, there is no one single
field that is unique. The fields are either Text or Date/Time
formats.

Can I use the Text field which contains the patient's Account number
and a Date/Time Field as a dual primary key? Will this work when
appending New data?

I have sequenced all the actions in VB applied to an "OnClick" event
in a command button. Using the TransferText method followed by update
and Delete queries for each report I have configured the Date/Time
data to concatenate and the produce reports I need. This final Append
Data so I have one single table holding all the unique records will
complete the project. Any Advice or ideas will be appreciated. Thank
You.

 

[Douglas J. Steele]

You can use up to 10 fields in a single index, so yes, you can have a PK
that contains the 2 fields you describe.

 

[John Vinson]

I am building a database to be used for healthcare data. The Data
comes from imported txt files and combines information from several
reports through queries. I am working on configuring the new
information to be appended into a Master Table each time. This will
require a primary key which I do not have yet, there is no one single
field that is unique. The fields are either Text or Date/Time
formats.

Can I use the Text field which contains the patient's Account number
and a Date/Time Field as a dual primary key? Will this work when
appending New data?

You can use up to TEN fields as a joint Primary Key. An account number
and a date should work just fine, with one possible exception:
Date/Time fields are Double Float numbers accurate to microseconds,
but only display to the nearest second. It's just *faintly* possible
that you could have two records for the same account, and *visibly*
apparently for the same date and time, but the time would be different
at a sub-second level, and not seen as a dup.

I have sequenced all the actions in VB applied to an "OnClick" event
in a command button. Using the TransferText method followed by update
and Delete queries for each report I have configured the Date/Time
data to concatenate and the produce reports I need. This final Append
Data so I have one single table holding all the unique records will
complete the project. Any Advice or ideas will be appreciated. Thank
You.

Sounds like you're on the right track. What provision will you make to
investigate and correctly handle duplicate records? If you have
mulitiple data sources, I could imagine cases where you have two
records with the same Account and Date (with no time portion) which
are in fact different; is this an issue to be handled?

John W. Vinson[MVP]

 

[Jeff C]

You can use up to TEN fields as a joint Primary Key. An account number
and a date should work just fine, with one possible exception:
Date/Time fields are Double Float numbers accurate to microseconds,
but only display to the nearest second. It's just *faintly* possible
that you could have two records for the same account, and *visibly*
apparently for the same date and time, but the time would be different
at a sub-second level, and not seen as a dup.


Sounds like you're on the right track. What provision will you make to
investigate and correctly handle duplicate records? If you have
mulitiple data sources, I could imagine cases where you have two
records with the same Account and Date (with no time portion) which
are in fact different; is this an issue to be handled?

John W. Vinson[MVP]

The database DOES hold multiple values for account number but there
can only be one test at a given time, thus the double primary key. The
time and date calculation comes from Doug Steeles DateDiff function
which returns an hourly value for patient's age which works very
well...Thanks Doug. I thought that after importing the data and
cleaning it up I would add lines to my sequnce involking an append
query which will move all the data into a table holding previous data.
If I have the primary keys set up any duplicates should drop out,
right? Thanks for your advice John.