Command to add a record into a table based on the data entry in another

[faithneal]

I am designing a database to capture a medical audit. There are more than 255 items per patient encounter, so I had to create more than one table, with the patient's encounter # as the unique identifier across all tables, at a one-to-one relationship. I created a navigation form with all the forms. Unfortunately, when I move from form to form, it doesn't stick to the same patient encounter. So what is the simplest way that when I start capturing information the patient's encounter on the first form, how do I automatically create the "record" in EACH table and prepopulate the patient's encounter # on each form ? (I understand folks' opinion that more than 255 items is too many items to collect, but that is the reality).

[PhilOfWalton]

If I understand you correctly, the usual method of designing a database like this is to have one table for the FIXED details of a patient (Name, Address, Date of Births, Gender etc) and a second table listing all the visits, with the PatientID as the Foreign Key

The main form shows the Patient's main details, and a continuous subform shows all the visits with information like VisitDate, Reason, prescription etc.

There are no limits to the number of visits per patient.

Phil

[faithneal]

Each visit has more than 255 items so more than what can be contained within one table or within one subform. Also for each visit there can be more than one service and for that service more than 255 data elements may be collected.

[PhilOfWalton]

I think a concrete example might help. Examples of what you mean by a service and what you mean by a data element.

Phil