Is there a way to simultaneously add new records to multiple (related) tables at once

[RDINSMORE]

This should be a simple question, but for some reason I can't find a straightforward answer online.

I have a database to keep up with academic information for students and, as you can imagine, this involves many many many fields. More than Access will support in a table.

I started out with a table that used the student ID as the first field and PK and then included more than the 255 fields allowed by Access. I split the table into a main table and 4 subtables, each using the student ID as the PK and established a 1 to 1 relationship between the main table and each of the subtables with referential integrity enforced. Obviously, my preference would be to keep this as one big table, but Access just won't let it happen.



I assumed that when I added a NEW student to the primary table, a matching new record would be added to each of the subtables. Apparently, that assumption is wrong. Do I actually have to manually add each record to EACH of the 4 subtables? There seems to be no other way of doing this, but having to remember 4 additional steps each time I get a new student is insanely annoying (and easy to forget)!


Any suggestions would be appreciated.

[pbaldy]

What do all the fields contain? That many fields is virtually always a sign of a normalization problem.

Fundamentals of Relational Database Design -- r937.com

[RDINSMORE]

Because the database involves academic information, I have around 180 possible classes a student could take. For each of those classes, I need a field indicating each of the following: (1) semester taken (2) grade earned (3) substitution (if applicable) and (4) transfer institution (if applicable). I also have to keep track of admission date, GPA, contact and demographic info, etc. So my primary table includes the contact, demographic, and other general academic info for each student and the 4 sub tables contain the 4 categories enumerated above. When I take in a new student and enter a new record in the form linked to the primary table, I want a new row added to each of the 4 sub tables so that those tables are ready to receive data. I have forms that contain fields from all 5 tables (program checklists that include grade, semester taken, subs, and transfer info) and those forms work fine for the students whose data was all in the excel spreadsheet I imported to make these tables. But for new students I add, I can't open the form unless I open up each of the 5 tables and manually add a line with their ID as the PK. Then it works fine. Just seems like creating a 1-to-1 relationship and checking "cascade update" would make those tables uniform automatically by, you know, updating them.

[June7]

Have to run 4 INSERT sql actions.

This can be automated with VBA.

However, you said two of them are 'if applicable'. Why create record if don't know it's even needed?

Should be using form/subforms arrangement for data entry/edit.

[pbaldy]

I guess I'll get out of the way, but the design is flawed. Read the link for more info.

[RDINSMORE]

"However, you said two of them are 'if applicable'. Why create record if don't know it's even needed?"

What I mean is that it's applicable for some students and not for others. Some take all classes with us and some transfer courses. The record is the student and the "transfer" or "substitution" field is the field. The only way to have a field available is to make it available (hypothetically) for everyone. So, my forms will include the whole list of classes required for a particular degree and those 4 fields for each class.

[June7]

I agree with Paul - multiple similar fields is not normalized structure and will likely cause problems. Should be a record for each enrolled class in a related dependent table. This is a many-to-many relationship - each student can enroll in many classes and each class can have many students.

Your preference for one big table is not obvious to an experienced database developer. For that arrangement, could just use an Excel spreadsheet.