Help needed for database / form design

[zonker]

Hi,



Very new L board here, I was wondering if someone could guide me as to how best to design a database / form as follows

The purpose is to maintain a database for procedures done in a hospital and print reports off it.

I have a table with fields such as patients name, age , procedure type etc. I have a form for data input for this.
Some types of procedures require referral to another specialist centre, for e.g surgery etc. What I am trying to do is generate a referral letter to this hospital using data fields from the table / form. Some of the data for this referral letter is not included in Table1. e.g

Table1
Patient name :
Age :
Procedure type :
Findings :

Referral to surgery letter needs to include all the above data, plus the following NEW fields which I wish to keep on a separate Table.

Table2
Patient name :
Age :
Procedure type :
Findings :

Referred to : (doctors name)

Referral centre :
Referred by :

Reason for referral : etc..etc..


Questions :

1. Is it best to have a second Table for this extra data for the referral letter?

2. Is it best design a second Form , or a subform to input data for this referral ?

3. If maintaining a second Table, how do I synchronise the common data, eg patient name, Age etc between the two forms ?



I'm sorry if my queries are not clear, tried my best to explain. Any advise will be highly appreciated. Thanks a lot.

[Rod]

Your patient details should be kept once, and once only, in your database. I recommend you change age to date of birth and calculate age each time you need it.

I assume that not all patients are referred but that it would be possible, over time, for a single patient to be referred more than once. This suggests a separate referral table related back to the patient table. The relationship takes care of what you call 'synchronisation;' obtain all the patient data from the patient table, do not duplicate it in the referral table.

How exactly do you intend to produce the letter? Is this a standard format that never changes?

[hapm]

I would even split up Table1 into a patient table and a session table, as I think a patien can have more than one visits, and therefor more then one procedures and findings.

[Rod]

hapm - well spotted