How to update 2 tables at the same time?

[ravikiran]

Hi Gurus,

I have some expertise in Excel and VBA, but never used Access till date.

I am designing a small access/excel based application for a school. I am able to tackle most of it, but the database table became too long to be managed well. I know that's not a good practice to follow.

I am planning to split the large database into manageable tables. However, I have problem in understanding how to apply logic while updating two tables at the same time.

I took care that there is just one primary key for all tables put together (Student ID). So when I update Student Profile as well as Student Marks tables at the same time, what is the logic that I need to follow?

Any reference to the right example (even an vba file) will also help me. I will be able to comprehend and apply it to my case.

Thank you very much,

Regards,
Ravi Kiran.

[alansidman]

What do you mean by updating tables? Are you using forms as your input? HOw are you updating your tables. Perhaps some examples of what you are trying to do. I don't understand your issue.

[ravikiran]

Yes Alan. I am using Excel to input the data into Access.

For every Student ID, there will be several sections (Profile, Marks, Attendance, Special Requirements etc.,). The main worksheet has an option to add a student ID. There will be a block with an update button for each of the above mentioned sections. On clicking the Update button, I retrieve the form with information (if already updated) or else with blank fields.

So if I create two tables Main tables (with Student ID, Name and Class as columns) and Attendance (few more columns), if I add a new student, what should I do?

Should I add the student ID only to the Main table or to both Main and Attendance tables with the columns in Attendance left blank.

I ask this because, I can figure out the coding part, but am unable to understand the standards by which I should code

Thanks for your help.

Regards,
Ravi Kiran.

[Kyle123]

You'd just add them to the Student Details table, not the attendance table unless you needed to input their attendance.

I'm guessing that you've got student ID as the foreign Key in your attendance table?

Might be worth re-visiting the key you've chosen, is it possible that the Student ID could change? What if someone miss-types it? - You shouldn't change primary keys, it's much better they are meaningless and auto generated so there would never be a situation where they would need changing

[ravikiran]

Thanks Kyle. I'll follow that suggestion regarding the auto generated Student ID.

And will add the new entry to each section only when the respective section is updated.

Thank for the help.

Regards,
Ravi Kiran.