View Single Post
  #1  
Old October 31st, 2008, 12:49 PM posted to microsoft.public.access.tablesdbdesign
KateB
external usenet poster
 
Posts: 75
Default Change of address

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?