Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2011
    Posts
    2

    Unanswered: Adding records into multiple tables at a single moment

    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!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes its possible, but is it worth doing.. almost certainly not.
    I think you should rethink your design
    why
    because you are confusing the role of a person with a person
    you may need to look at the sub/super type to understand

    if you have a common form, which theoretically can support data entry / capture of 4 different entities, then to me it suggests either there should be just the one entity or one common table and 4 sub tables containing stuff pertinent to the specific role
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    2
    Sorry, I really don't understand what you mean...

    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.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so there is no difference between Admin and Teacher
    Pupil has 2 extra columns (batch and Section)
    User has 3 additional columns
    presumably an admin, teacher, pupil are all users. if so you dont' want to repeat data. look at normalisation.

    user doesn't need an ID, as the userid should be unique enough, unless you allow multiple users to use the same userid.

    I would expect the userid to an attribute of person, likesie the password could be stored there as an encypted value. although personally I wouldn't touch a selfd designed table in Access/JET for security. if i want to authenticate users then I'm perfectly happy using the network logon (google 'dev ashish api')

    you dont' say what the results table is but Im guessing you will only need the priamry key from pupil as the foreign key in results.. depending on how you design your tables.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •