Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Kearney, Nebraska, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    One to Many Rel. + AND statement in query (2000)

    I have an access query question that I don't know the answer to.

    Example:
    One to Many Relationship between tblStudents and tblClasses

    tblStudents
    PkSID Name
    ----------
    1 - Joe
    2 - Mary
    3 - Bob

    tblClasses
    PkCID FkSID Subject
    --------------------------------------
    1 - 1 -English
    2 - 1 -Math
    3 - 2 -English
    4 - 2 -Sociology
    5 - 2 -Computers
    6 - 3 -English
    7 - 3 -Math

    I want a query that pulls only those students with BOTH English AND Math as classes. The correct results in this example would show both Joe and Bob because they are taking BOTH English AND Math; it would not show Mary because she only has one of the subjects. I can get the OR statement working with no problem which but this is throwing me for a loop. As a sidenote, the Subject list could be infinite. Any help would be greatly appreciated.

    Thanks,
    Shane

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

    Re: One to Many Rel. + AND statement in query (2000)

    Create a query. Add tblStudents, then add tblClasses twice, the second one will have an alias tblClasses_1. Join tblStudents to both copies of tblClasses on PkSID <--> FkSID.

    Add PkSID and Name from tblStudents to the query grid, and Subject from both copies of tblClasses. Set the criterion to "English" for one of them and to "Math" for the other. Clear the checkbox to display them. That's it.

    Note: I would set up the tables slightly differently, with an intermediate table tblStudentClasses that contains a composite primary key indicating participation in classes.

    tblClasses
    --------------
    PkCID Subject
    1 English
    2 Math
    3 Sociology
    4 Computers

    tblStudentsClasses
    --------------------------
    FkSID FkCID
    1 1
    1 2
    2 1
    2 3
    2 4
    3 1
    3 2

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Kearney, Nebraska, USA
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: One to Many Rel. + AND statement in query (2000)

    Worked like a charm! Many thanks!

Posting Permissions

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