Table relationships help

[PRodgers4284]

I am working on a project atm that uses 3 tables with relationships between them. The tables are as follows:

Patientdetails tbl:

#PatientID (text field) input by user.
forename
surname
address etc.
age
date
etc

Admission tbl:

#EpisodeID (autonumber)
PatientID (text field)
unit
hospital
ward
consultant
etc

Patientquestion tbl:

#EpisodeID (number field)
PatientID (text field)
Trustname
Date
etc

I basically want the episode id to appear in the EpisodeID field in the patientquestion tbl, at present i have to enter the EpisodeID in manually which isnt very efficient. The users at present start by added a record to the patientdetail table with an patientref number for example H123, then an episode is added to the admissions tbl and the patientref number is input by the user along with other details of the patient admission. Then the user adds a record to the patientquestion tbl by inputting the episodeID created by the admissions tbl. The database needs to allow a patient to have 1 to many episodes as they may visit many times etc.
What i want to know is there a way a can add/update the EpisodeID to the patientquestion tbl without having to input it everytime for each visit? I dont know whether i have explained this properly but any help is much appreciated.

The table relationships are in the attachment below.

 

[thingssocomplex]

Hi I am not fully sure I understand your query, it however appears you want to update a field in a table to another table, you could do this by using a append query.

 

[PRodgers4284]

Hi I am not fully sure I understand your query, it however appears you want to update a field in a table to another table, you could do this by using a append query.

Hey thanks for your help, can you provide anymore info on the append query and how i would go about creating it?

 

[boblarson]

Instead of an append query you can just use a main form and subform (or subforms).

 

[thingssocomplex]

Hi, I am still very much a novice myself and "boblarson" as far more knewledge then me so I would use is preffered option, if you need help with subforms I can assist you with this!

 

[Pat Hartman]

Questions/Observations:
1. Patient questions has a 1-1 relationship with the episodes table. If that is the case, why did you decide to separate the data into two tables when they could have been in one?
2. "Date" is a poor choice for a column name since it is also the name of a function and that will cause confusion because Access may not be able to determine if you are referring to the "Date" function which will return today's date or your "Date" data field. There are published lists of reserved words that will tell you what names will cause conflicts in VBA and SQL.
3. If the question table is related to the admission table, it should not include PatentID since that can be inferred by a join of the two tables.