Same record/Primary key in multiple tables

Oct 30, 2013
Reaction score
Hi all,

I am new to the world of Access so please bear with me!
I am trying to improve the design of a database that is used to routinely store patient data in a hospital ward... this includes new referrals (patient demographics), admission data (e.g. weights and several questionnaires) and discharge data (again the same questionnaires + extra data on post-discharge arrangements).
So as soon as a new patient is referred to our service, I assign them an ID and enter it in a Referrals table. If the patient is admitted I then need to enter a lot of other data, including questionnaire items that the patient has completed. Currently, this is in another "Measures" table, but the patient ID needs to be the same as in the Referrals table.
At the moment it is very easy to make a mistake as I haven't yet found a way to create a form that can update these multiple tables simultaneously. I have tried to have a form (e.g. Referral data) with subforms (admission questionnaires; discharge questionnaires) but, because all tables must have the same patient ID as primary key, I can't enter data in the subforms unless I first create the appropriate patient record in the table first. So to summarise:

- I have a database to store patient data
- All data only refer to the patient, so patient ID needs to be primary key in all my tables
- I would like a user-friendly way to update patient records from forms without having to enter the primary key multiple times.

Hope somebody can give me some guidance!!


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question