medical database

[conoroneill]

I am creating a medical database and I am using a main form and a sub form. (patient details and attendances)
I want to use it so that when a patient is created in the main form with a unique "Patient number" as the primary key that this number is linked to their firstname last name :
date of birth :and address so that
1. when on the sub form for attendances if you type in patient number the rest of the details come up automatically and
2. that you cannot have 2 records with the same firstname last name :date of birth :and address etc.

any ideas ????

 

[Fizzio]

This is the very heart of relational Db design, creating tables that store items relevant to that data and linking the 2. Your initial form structure is no doubt correct but you must ensure that your data structure at table level is correct first.

With each ID eg NHS number, Social Security Number or even access generated autonumber, the rest of that data in that table will be dependent on that ID so that you only ever need to store that ID in linked tables to refer to that patient.

To avoid having patients with the same name, DOB etc., you can set up an index in the table to not allow duplicates. The MS samples have excellent examples or browse / search the forums here for some good examples of different Db designs.