Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    subquery syntax (2007)

    I am trying to write the following query:

    SELECT tblStudents.txtName, tblStudents.StudentId
    FROM tblStudents
    WHERE not exists (SELECT fidName
    FROM tblAttendance
    WHERE fidClass=7);

    This part gives me 201 records :
    SELECT tblStudents.txtName, tblStudents.StudentId FROM tblStudents

    this part gives me 99 records:
    SELECT fidName FROM tblAttendance WHERE fidClass=7

    How do i put it together to get the names o the 102 people who are not in class 7
    fidname is the foreign key of studentID

    Thanks

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

    Re: subquery syntax (2007)

    Try

    SELECT tblStudents.txtName, tblStudents.StudentId
    FROM tblStudents
    WHERE tblStudents.StudentId Not In (SELECT fidName FROM tblAttendance WHERE fidClass=7);

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: subquery syntax (2007)

    I had tried that and I get no records even though the two parts of the query work. Still no records. attached is the back end zipped if that helps
    Thanks
    Attached Files Attached Files

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

    Re: subquery syntax (2007)

    I cannot open .accdb files, sorry. I don't have Access 2007.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: subquery syntax (2007)

    Sorry her it is as an mdb
    Attached Files Attached Files

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

    Re: subquery syntax (2007)

    The problem is that tblAttendance contains records with null values for fidName and/or fidClass. You should remove those and make the combination of fidName and fidClass the primary key of tblAttendance to prevent this from happening in the future.
    Once you have removed the incorrect records, the query returns the records you want. See attached version.
    Attached Files Attached Files

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: subquery syntax (2007)

    thanks. Will try to figure out whay that is true when I can think

Posting Permissions

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