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!

[June7]

Why do you have 3 tables for people? Are the fields of these tables significantly different?

You might be able to do a form/subform arrangement. No code would be required. Just don't have enough info about your data structure. Otherwise VBA code will be need to run an sql INSERT statement to save record to appropriate table.

[bpen91]

Because the admin,pupil and teacher tables will also contain different fields(for ex. section, batch for table Pupil) and will be related to other tables(for ex. Results) and our teacher don't want us to create a user and pass field for every table.

Is there another way to approach this problem??

[June7]

Post diagram of data schema for analysis. Or the project itself.

Regardless of your data arrangement, the choices are basically what I already offered.

[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.

[June7]

So users can be Admins and Teachers and Pupils? You should not save names in all tables. Names should not be the primary/foreign keys. Still don't understand why three tables for people. I suggest one table for all people with basic info and child tables for specialized data. Consider this:

Users
ID (primary key)
FName
MName
LName
UserName
Password
Type (what is this - Pupil, Admin, Teacher?)
Gender

PupilResults
UserID (foreign key)
Batch
Section