Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Sep 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2000 pulling info from 2 tables (2000)

    I'm creating a database for classes - I have one table with everything in it. I know I should break it down. I want to set up a separate table for the trainers/dates/locations. Then when I register somebody, I want to be able to pull that information in somehow without having to type it each time because I know that's not the efficient way to do it. <img src=/S/help.gif border=0 alt=help width=23 height=15> Thanks!

  2. #2
    New Lounger
    Join Date
    Mar 2002
    Location
    Maryland, USA
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 pulling info from 2 tables (2000)

    I have a file that has 2 tables. When you need display your information you can choose both tables in a query and then create a form from your query. Make sure you choose both tables in your query and choose the fields you need. Or you can link the fields together.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 pulling info from 2 tables (2000)

    You really need a minimum of 3 tables. Each class will be taken by many students and each student may take many classes. With only two tables you would have a many to many join. In cases like this you need a junction table containing the class number and the student number for each registration.

    Registration can be entered using a student form with a classes subform. You would base your classes subform on a query of your junction table and your classes table. The only data entry required would be selecting the class from a combo box in the subform that the student wants to take.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    New Lounger
    Join Date
    Sep 2001
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access 2000 pulling info from 2 tables (2000)

    I created a separate table with the class location/date, etc. information. Then I created a 3rd table (join table) with only the ID from the registration table, and the IDLocation from the classes table.
    Then I tried to create a subform for the class locations. I want to have a drop down to choose which class they will attend, and have it fill in the fields for that class.

    I got the subform, I see a list of all the classes (11 currently) and I can choose one and it fills in the fields.

    But when I move on to the next registrant, when I click on a different class from the list, it changes the previous registrant too.

    In the registrants table, I took out the fields for class location, time, date, trainer. I'm thinking I needed to keep them so it has somewhere to STORE my selection from the subform -- I feel like I'm right on the edge of understanding this, but can't see it.

    Thanks! Peggy

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access 2000 pulling info from 2 tables (2000)

    It sounds like you're using a continuous subform with a combobox on it. That always confuses people because, although it looks like there are multiple comboboxes, there is really only one and it's bound to the current record. When you select an item in the combobox, the other records all show the same value even though they haven't really been changed. For that reason, I never use bound comboboxes on a continuous form or subform.
    Charlotte

Posting Permissions

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