Adding Record to Multiple Tables

[rushilc]

I have a main table with subject id and other fields. I also have 16 other tables which have questionnaire information at 17 different timepoints. In those tables, the subject id is the only connected field between the main table and 17 different years. When I add a new record (by subject id) on the main table, I want it to add that same subject id to the 17 other tables because that subject id will eventually get all 17 years worth of data. But even using subforms, it will not add a new subject to the other tables unless I add some sort of information such as (date received) into the subform. Is there a way around this?

 

[Isskint]

Your table structures seems a bit strange, however the answer would be append queries. Either set up 17 queries (1 for each table to update) OR create the SQL on the fly in a loop. Either way the SQL would look something like:

Query:
INSERT INTO QuestionnaireTableX (SubjectID, DateField )
SELECT Forms!NewMainSubject!SubjectID, Date() AS Expr1

SQL on the fly:
INSERT INTO QuestionnaireTableX (SubjectID, DateField ) VALUES (Forms!NewMainSubject!SubjectID, Date())

 

[rzw0wr]

The other 17 tables will not set up a relationship field (Number) until a record has been made it the table for that ID.

You also need to Google Access table normalization. I think your tables may need it.
It appears to me that you are going to store the same data in the 17 tables. This can be done with less tables.

Dale

 

[rushilc]

Thanks! I am going to try that, I looked up some normalization tutorials, and a lot of them were about just getting rid of repetitive data such as gender, however, I am trying to store different information for every year (which are all different tables), is that not an efficient design? Sorry, I am new to Access, and just working as a volunteer summer student.

 

[Isskint]

If you are storing data for different years, just use on table and add a Year field.

Eg lets assume each of your questionaire tables has:

SubjectID
Queston1
Answer1
Question2
Answer2
etc

Just i table with:
SubjectID
QuestionYear
Queston1
Answer1
Question2
Answer2
etc

 

[rushilc]

If I do that, what happens if I have a subject that has two questionnaires, one in year 1 and one in year 2? What would be the primary key of this combined table if not subject id?

 

[Isskint]

Well subjectID is (i guess) already a PK in the main table, so its occurrence in any other table should be as an FK. So in the combined table, add an extra field as the PK - SurveyID? Each Survey is unique (even if it is the same subject, the year is different anyway).