Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    56
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Relationships (Access 2000)

    Hi,
    I have two tables one named Courses, with one of the fields being Course ID, and another table called Students also with one of the fields being Student ID. The relationship is made between these two tables by an intersecting table called Cou-Stu. The Cou-stu table only contains a Course ID field and Student ID field. I will be entering information on a student in a form that will have the Student ID field from the student table and the Course ID field from the Course table (so the form will probably be created from a query). My question is how do I automatically uptake the Course ID and Student ID field in the Cou-Stu intersecting table when I enter info about a student? I hope I have explained it clearly enough. I was thinking of using an SQL Insert statement but not sure the best way of doing this. Thank you.

    Dorothy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Relationships (Access 2000)

    The best way to handle this is with a form and a subform.

    One approach is to create a form based on Students with a subform based on Cou-Stu, or on a query based on Cou-Stu and Courses. The subform will be linked to the main form by Student ID, through the Link Child Fields and Link Master Fields properties of the subform as a control on the main form. The subform can be a single form, but a continuous form is probably better. Each record in the subform represents a course taken by the student. When you select a course in the subform, the Student ID from the main form will automatically be filled in. No code or SQL is needed.

    An other approach is to create a main form based on Courses with a subform based on Cou-Stu, or on a query based on Cou-Stu and Students. The subform will be linked to the main form by Course ID, through the Link Child Fields and Link Master Fields properties of the subform as a control on the main form. The subform can be a single form, but a continuous form is probably better. Each record in the subform represents a student that takes the course. When you select a student in the subform, the Course ID from the main form will automatically be filled in. No code or SQL is needed.

    These approaches are not mutually exclusive, you can use both.

Posting Permissions

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