Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2015
    Posts
    6

    Unanswered: Filtering a report to show students who failed most recent exam plus 1 previous exam

    I have created a table with exam codes (these are based off consecutive date numbers, ie "Exam20151021" for the exam on October 21, 2015) for each exam session, followed by student ID number and the score. Tables were also made containing the student names matching the ID number and the dates of the exams matching the exam code. I created a query to gather the information from these tables and produce reports.

    I am trying to use the same report but have it be filtered to only present names of students matching certain criteria.

    If the score for the most recent exam (ie, the maximum exam code) is at or below 69 AND the student has scored at or below 69 on any ONE previous exam, the student needs to show up in the At Risk Report.

    If the score for the most recent exam is at or below 69 AND the student has scored at or below 69 on any TWO previous exams, they need to show up in the Academic Board Report.

    How would I code this? Would I enter it on the filter settings in my query, or somewhere else?

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Get the most recent exam...and the failure.
    Select student, max(examID) from tExams where [grade]<70

    So get the TOP 2 exams, sort desc. And their grade.

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I would do is

    Create the two Query to find the outcomes then Union them

    as you go to print them
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Nov 2015
    Posts
    6
    Thanks for the replies.

    Here is what I did:

    Create a query containing:

    ALL data from my Exam Dates table, which shows all exam dates and the corresponding exam codes.

    Data from Exam Scores table, showing student ID number, exam code, and score. Create FailCount of 2 where exam score is <=69. Show Min and Max exam codes.

    For the report, it will show ALL exam dates, with a notice message if there are no failing students for that exam date.

Posting Permissions

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