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.


"KateB" wrote in message
...
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

"Jeff Gaines" wrote:

On 31/10/2008 in message
KateB wrote:

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).

--
Jeff Gaines Damerham Hampshire UK
There is no reason anyone would want a computer in their home.
(Ken Olson, president Digital Equipment, 1977)