Results 1 to 5 of 5
  1. #1
    usman400 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    2

    Selecting records based on particular value

    Hi,
    I have three columns in my access database table:
    student_id, name, result
    the student id is not unique (it is not the primary key of the table)
    Now the situation is, there can be more than one record against


    one student id e.g.

    student_id, name, result
    STD1, John, Pass
    STD1, John, Absent
    STD1, John, Repeat

    or two records for one student id e.g.
    student_id, name, result
    STD2, John, Absent
    STD2, John, Repeat

    I want to get the records where student is not yet passed i.e. it is absent or repeat in the result column.
    So if the student is passed but Absent and Repeat rows also exists, then I dont want to select this student.
    If a student is Absent or Repeat and Not passed, then I want to select
    all such students

    plz help, I am using MS Access 2003

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    In your examples - are STD1 & STD2 - John - the SAME Student?
    In other words - does the same student - John - have two different student_id's?

  3. #3
    usman400 is offline Novice
    Windows XP Access 2003
    Join Date
    Sep 2011
    Posts
    2

    No

    John is a separate student, in my 2nd example records, please consider
    it as Henry instead of John
    The point is for the same student (with same student id) there can be
    more than one records based on result value

    e.g. In 1st example, John has all three results, so I dont want to select
    him as he is passed in one of its results.

    In 2nd example (consider that henry instead of john), I want to select
    henry as he is not yet passed

  4. #4
    goodguy is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Dec 2010
    Location
    Zanzibar, Tanzania
    Posts
    229
    Wrong design, Usman400. You will not achieve any utility with this sort of design. Unless there is a differentiating column like Subject, the records as they are now do not make sense. Also, why is StudentID not set as PK?

  5. #5
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    usman400,

    This may not be the most elegant way to do this but it will get you what you need.

    You will need to create 3 queries with the following SQL in each of them.

    1. QryNotPassed:
    Code:
    SELECT Table1.StudentID, Table1.StudentName, Table1.Result
    FROM Table1
    WHERE (((Table1.Result)<>"Passed"));
    2. QryPassed:
    Code:
    SELECT Table1.StudentID, Table1.StudentName, Table1.Result
    FROM Table1
    WHERE (((Table1.Result)="Passed"));
    3. QryFinal
    [You have to name the first two queries with the names that I gave them in order for this one below to work. You can have different names for the above two queries - but you'll have to change the names accordingly in the SQL below]:
    Code:
     
    SELECT QryNotPassed.StudentID, QryNotPassed.StudentName, QryNotPassed.Result
    FROM QryNotPassed
    WHERE NOT EXISTS
    (Select QryPassed.StudentID 
    From QryPassed
    Where QryNotPassed.StudentID = QryPassed.StudentID);
    I have a feeling that this can all be done in one query - but I do not know how. I'll try & find out.

    Let me know if you have any questions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Selecting records within a form
    By crowegreg in forum Programming
    Replies: 29
    Last Post: 08-19-2011, 08:18 AM
  2. Selecting a corresponding table field based on text field.
    By michaeljohnh in forum Programming
    Replies: 5
    Last Post: 10-08-2010, 10:33 AM
  3. Selecting multiple records in drop down
    By rscott7706 in forum Access
    Replies: 1
    Last Post: 08-26-2009, 03:00 PM
  4. Problem Selecting Records for a Report
    By Joe in forum Programming
    Replies: 0
    Last Post: 09-27-2008, 02:27 PM
  5. Replies: 1
    Last Post: 03-17-2006, 12:04 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums