Adding records into multiple tables at a single moment

[bpen91]

Hello guys! I'm an ms access newbie here and have a question to ask..

Scenario:

Supposing you have 4 Tables
1.)User
2.)Admin
3.)Teacher
4.)Pupil

Now the "User" table has the ff. fields: ID(auto num),FName,MName,LName,User,Pass,Type.

Now what I want to do is, if I add a new a record in the table "User" and if the record has the data "Admin" in the field "Type" then the record should also be saved in the table "Admin" but if the data is "Teacher" or "Pupil" then it should be also saved in their respective tables.

Question is: Is that possible? If yes, then how??

Sorry guys for the trouble and thanks for the help!

 

[jdraw]

You add(APPEND new record) a record to 1 table at a time. You Update(UPDATE existing record) 1 table at a time.

 

[bpen91]

I'm sorry I don't understand what you mean. Is it in the query design?? Please explain more..

 

[smig]

any reason to save the same data in multiple tables ?
This is against all normalizations logical

 

[bpen91]

Here's the complete design:
"User" table has ID(auto num),FName,MName,LName,User,Pass,Type.

"Admin and Teacher" table has ID,FName,MName,LName,Gender

"Pupil" table has ID,FName,MName,LName,Gender,Batch,Section.

Now this "Pupil" table will be used for the "Results" table too.

Our teacher told us not to put "Username" and "Password" fields for each table so that's why I decided to use another table "User" for the username and pass.

I don't know how will I do it with the Relationship since the "Type" should be determined first before adding the record into the other table.

 

[wiklendt]

if you want to see which users are either admin or teachers, just run a query with the info you want to see - there's no need to save the same data in multiple locations, as smig said, to do that goes against good database design.

 

[DevastatioN]

As stated above you should only have one table tblUsers.

In this table include FName, MName, LName, Gender, Type, Username, Password.

Type field should be used with a dropdown as Admin, Teacher, Pupil.

You no longer need tblTeachers, tblAdmins, and tblPupils. Anytime you only need one or the other, you just run a query to find them (if it's a dropdown looking for pupils, the dropdown will filter for only pupils in tblUsers).

I'm not sure what Batch and Section are, you can add them to the table if they belong there, or make another table with this info depending on the relationships.