Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Question (Access 2000)

    I've got a table with 2 fields: StudentID and Pass Flag
    There are duplicate studentID's and pass flags can be null, 0,1, or 2
    I'm trying to find only the studentID's that are null which means they have never been in a class.
    In what I've tried so far I can get the nulls but if I do a search with the StudentID of one of the nulls It shows they have been in another class because there is a pass flag of a 0,1,or 2.
    I welcome any helpful ideas
    Thanks,
    Scott

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

    Re: Query Question (Access 2000)

    What is the table supposed to represent? With only a studentID and a Pass field, it doesn't look as if you have enough fields to be able to identify anything uniquely. You say there are duplicate studentIDs, so I don't know how you can make sense of this data without a field to indicate a class. Perhaps if you explain where it comes from, someone will be able to make a suggestion.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Atlanta, Georgia, USA
    Posts
    274
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Question (Access 2000)

    If they have a null in the Pass field and no value anywhere else in the Pass field they have never been to a class.I wanted to identify those students.
    I found a way to do this albeit not the most elegant way:
    SELECT tblTR_CLASS_ROSTER.student_id, Sum(IIf([pass_flag]="",1,0)) AS [NULL], Sum(IIf([pass_flag]="0",1,0)) AS 0, Sum(IIf([pass_flag]="1",1,0)) AS 1, Sum(IIf([pass_flag]="2",1,0)) AS 2
    FROM tblTR_CLASS_ROSTER
    GROUP BY tblTR_CLASS_ROSTER.student_id
    HAVING (((Sum(IIf([pass_flag]="",1,0)))=0) AND ((Sum(IIf([pass_flag]="0",1,0)))=0) AND ((Sum(IIf([pass_flag]="1",1,0)))=0) AND ((Sum(IIf([pass_flag]="2",1,0)))=0));

Posting Permissions

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