Hi John,
Thank you for your response, it is my objective to replace the doctor's name
and practice with respective IDs. However, I don't know the steps on how to
replace the doctor & practice names with ID in the transaction file.
Following are the field names, data type and a sample of the data. There is
no primary key to the transaction file which I need to define.
Comments inline.
Field: Data Type: Data
Name: TEXT: Patient Last, Patient First
Two problems here: NAME is a reserved word and should not be used as a field
name; and you're really best off using separate FirstName/LastName fields. You
can use expressions like
LastName: Left([Name], InStr([Name], ",") - 1)
FirstName: Trim(Mid([Name], InStr([Name], ",") + 1))
to extract the two name fields.
ABC Medical Record #: TEXT: 1234567
Would this be a unique identifier for the patient? or for the visit? or is it
something else?
What's this? blank in all records? is it needed?
This should be put into a Date/Time field in the Patients table; is it Text
now?
Service: Ped Chest
Procedure: Ped Chest
You'll need a table of Services and perhaps a table of Procedures. Aren't
there standard insurance-company codes for the various services?
RefDate: 01-Mar-04
ApptDate: 04-Mar-04
Date/Time fields in the transaction table again
Maybe a small lookup table of status values
Practice: ABC Health Network Medical Center
Provider: Abad , Edda
Last Name: ABAD
First Name : EDDA
The Provider field is redundant and should be eliminated from the final
product - you can easily concatenate the first and last names.
ResTime: 0
CHN Category: CHN HHC
Is this one category, or two?
Do you need a table of facilities?
How are Providers related to Practices? Might one doctor work with two or more
practices, or does each doctor work for only one at a time? How about over
time: if a service were provided on one date by Dr. Abad when she was working
for ABC, and another service at a later date when she'd transferred to Edda
Abad LLC, would you want to track the Practice separately from the
practicioner?
Very importantly here: since this table will contain sensitive patient health
record data, are you aware of the very stringent HIPAA patient-privacy and
database security and validation requirements? There's some question whether
Access is even *capable* of providing the security required by these laws.
Does your budget or insurance cover the $100,000 plus fines for violations?
BUT.... to answer your question directly - you could create tables for
Practice and Practicioner:
Transactions
TransactionID <Autonumber, Primary Key>
PatientID <link to Patients, who was seen>
PractitionerID <by whom>
PracticeID <where>
RefDate
ApptDate
Status
Comments
<Other fields as appropriate pertaining to the individual visit>
Practices
PracticeID <Autonumber, Primary Key>
PracticeName <Text>
Practicioners
PracticionerID <Autonumber, Primary Key, or perhaps Text, Primary Key if you
have each doctor's tax ID or other unique identifier>
LastName
FirstName
PracticeID <Long Integer, link to Practices>
<other biographical data as needed>
Patients
PatientID <Text, Primary Key, maybe your Medical Record #>
LastName
FirstName
<etc>
<other tables as needed>
You can run Append queries selecting the existing data. Set the query's Unique
Values property to Yes so that each record will be unique. This will cause
problems when you have (say) two different patients who happen to have the
same name - Jim Smith and his father Jim Smith, perhaps. These will need to be
manually identified.
To get the PatientID into the Transactions table, TEMPORARILY put a unique
Index on the combination of FirstName and LastName in Patients. Create an
Update query joining the (filled) Patients and Transactions tables, joining
firstname to firstname, lastname to lastname. Run an Update query updating the
long integer PatientID foreign key to the autonumber value from the Patients
table (which will have been filled in by the append query). Do the same for
Practices and Practicioners.
IF - and I'd say it's a big If, given HIPAA - you want to proceed with this
(rather than buying one of the many good clinic practice databases already on
the market) and you need more help, don't hesitate to post back.
John W. Vinson [MVP]
