Change of address

[KateB]

I am a self-taught novice (the worst kind!) so am hoping I can get a nice
simple answer to my question. First the history:

I want to re-build a database I've been using for a couple of years. It
records patients and test results for specific infections. One patient can
have many tests at the same time which each have a unique number. Currently
the patient name, DOB, GP, 1st line of address, along with test details -
type of test, date of sample, reported date etc., are all in one table. This
means if John Smith has 6 tests on one day I have to enter all of his
personal details 6 times, so I am planning to pull that info out to a patient
table. As time has gone on extra info has been added. Previously I had no
unique identifier for a patient, so could never be sure if John Smith born on
01/01/1950 was the same as John Smith born on 01/11/1950 and it was a typo,
or a different person, but we will soon be getting a national identifier
which will help with data quality and confidentiality. So, on to my problem:

If I have a separate table for patient details how should I deal with a
patient moving home or changing doctor (GP)? It is possible that Mr Smith
could be in a nursing home for a period of time then go back home, so I need
to ensure tests are against the correct address (they could pick up an
infection in the home). Should I add a new entry for him detailing the new
address and then if he goes back home a 3rd entry which would be the same
address as the 1st entry? How can I then make sure that tests are reported
against the correct address when I do historical reporting? e.g.:

address test ID date
23 Oak Lane 1234 01/04/08
Old folks NH 1254, 4578, 6541 10/06/08
23 Oak Lane 5547 23/09/08

Do I need to give each address a unique ID and pull that into the table
containing test ID?

 

[Fred]

Kate,

As a side note, your "changing doctor(GP)" note implies that the doctor's
name is a part of your mission/question, but you didn't tell us anything
about that.

Another question: Do your have a requirement for recording a then-current
address for each test? Let's assume not.

Also, a cornerstone of this will be to have each patient uniquely /
correctly identified with a number, and have only one "patient" entry for
eadch patient. You have described a path you are taking to that point.
Let's assume that you have already accomplished this.

The this is going to have to start with you making a decision between Plan
"A" and plan"B". "B" is more work; you need to decided if it is necessary
or sufficiently useful to retain previous addresses.


Plan "A": Just keep a single address in the patient record/table, and keep
it current.

Plan "B" Make a second address table, and link it's entries to the patient
table on a PatientIDNumber field. Include a "CurrentLocation" field where
entery of a ""Y" (Yes) indicatres that that is their current address.

Hope that helps a little.

Fred

 

[Jeff Gaines]

Do I need to give each address a unique ID and pull that into the table
containing test ID?

What about an address table with 'From' and 'To' in it linked to the
CustomerID, you could then pull the address that was valid on a specific
date.

If you go that route I would be inclined to use a date index (number of
days from a global starting date) in an integer field as it is
quicker/easier to search on than DateTime (in my experience anyway).

 

[KateB]

Thanks Fred, I have answered each part below:

Kate,

As a side note, your "changing doctor(GP)" note implies that the doctor's
name is a part of your mission/question, but you didn't tell us anything
about that.

As with address, I need to know who their family doctor was at the time of
the test as the causes of the infection could relate to treatment given.
Therefore I thought this could be done in a similar way - or even in the same
table?!

Another question: Do your have a requirement for recording a then-current
address for each test? Let's assume not.

I need the address at the time of the test - if they were in a nursing home
there could've been an outbreak and that info would be lost if the patient
then went back home and the address overwritten.

Also, a cornerstone of this will be to have each patient uniquely /
correctly identified with a number, and have only one "patient" entry for
eadch patient. You have described a path you are taking to that point.
Let's assume that you have already accomplished this.

The this is going to have to start with you making a decision between Plan
"A" and plan"B". "B" is more work; you need to decided if it is necessary
or sufficiently useful to retain previous addresses.

Going to have to go with something along the lines of Plan B!

 

[KateB]

Thanks Jeff,

This is exactly my problem. I can record all the addresses, tests,
patients, etc, but am not sure how to tie the address to the correct test in
queries at a later time. I think a combination of yours and Fred's
suggestions may work for me. Lots of trial and error to come!

Kate

 

[Klatuu]

Kate,
Here is how I think you should structure it.
In that we have two moving targets, address and GP and that you need to know
both pieces specific to a test, and the each test could have either or both
of those items different for the previous test.

PatientIdentification.
Include name of patient and those things about the patient that will not
change like Birthday. Don't use the name as part of the primary key,
because that can actually change. Include in ths table a foreign key to the
PatientDemographics and Doctor tables so you know what the current items
are. In your application you can change this when a patient is moved.

PatientDemographics.
Addresses, phone numbers, email addresses, etc. This should have the data
items that can change and may be specific to a test.

DoctorTable.
The information about the doctores.

Test Table.
This would have relations to the PatientIdentification, PatientDemographics,
and Doctor Tables as well as information about ONE and ONLY ONE test. If
multiple tests are done on the same day, you would have a record for each
test.

And, in your application when you set up a test record, you need to
construct your form so it will show the current demograhpic and doctor
information but allow the user to use an exsiting address or doctor or
create a new address or doctor.

 

[Jeff Gaines]

This is exactly my problem. I can record all the addresses, tests,
patients, etc, but am not sure how to tie the address to the correct test
in
queries at a later time. I think a combination of yours and Fred's
suggestions may work for me. Lots of trial and error to come!

Perhaps something like:

SELECT * FROM tblAddresses WHERE PatientID = RequiredPatientID AND
AddressFromIndex <= RequiredDateIndex AND AddressToIndex >=
RequiredDateIndex

Ohmigod I didn't say SELECT * did I?

Could be fun, hope you've got time to experiment

 

[Fred]

Kate,

These guys know Access design better than I do. My strength is on the
implementation and organization of information side.

Just one "30,000' view" note to add. In your 2nd post you in essence said
that, for each test, your need to (amongst other things) record:

- The name or description of the test
- The patient's address at the time f the test
- The Patient's GP at the time of the test.

This means that the above 3 items are fundamentally a part of the test
record. How you get them into / store them in the record can be any of
several ways such as:

- Have theire most recent test record loaded as a default and then editing
it.
- make separate tables for any or all of the above three items, and use a
dropdown list to load the text*
- make separate tables for any or all of the above three items, and use a
dropdown list to load the ID# of the entry, and link to that table to show
the text.*

Or a combination of the above.

* For address, filter the dropdown list to show addresses associated with
the patient.

Sincerely,

Fred