Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    query needed to return latest test scores (access2000)

    I need a query to filter records and return only the latest test scores for each of the students-- children will have numbers of scores numbering from one to 20. Any good ideas will be appreciated

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

    Re: query needed to return latest test scores (access2000)

    I assume that you have a table with fields (among others)
    - Student ID or something like that
    - Date of Test
    - Score of Test

    We're going to create two queries.

    First, create a query based on the table; add Student ID and Test Date to the query grid.
    Make the query into a Totals query (View/Totals)
    The Totals option for Student ID should be Group By (default setting)
    The Totals option for Test Date should be Max.

    This query will return the date of the most recent test for each student. Save this query.

    Next, create a new query and add both the table and the query you just saved.
    Join them on Student ID and the date field.
    Add Student ID and Test Score.

    This query will return the most recent score for each student. It will omit all students that haven't done any test yet.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query needed to return latest test scores (access2000)

    Thank you-- will try this tomorrow-- it is a fairly complicated database but I was stumped on this issue. There will be a long series of reports to track the effects of daycare instruction on school readiness.

  4. #4
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query needed to return latest test scores (access2000)

    well, I set it up and the Totals qry returns the subset that I want although it did not eliminate the students with no scores (this will not be a problem as starting July 1 all children will have scores)-- however when I created the new qry-- it did NOT return the filtered set-- wondered if it mattered where I got student ID from and tried it both ways but no difference-- maybe I missed something. Normally I would have expected the results that you did; but it is not what I got-- I will keep working on it from this end but if you happen to think of something I might have done--please let me know

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

    Re: query needed to return latest test scores (access2000)

    Can you post the SQL for the queries you have created? Then I and/or other Loungers can try to find out what's happening.

    (If you don't know how to get at the SQL: Open a query in design view, then select View/SQL. Copy the text to the clipboard using Ctrl+C, then paste it into a post using Ctrl+V)

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query needed to return latest test scores (access2000)

    Thanks, I have probably done something stupid--but dont see it right away
    SELECT tblASQtesting.ChildID, tblASQtesting.ResultID
    FROM tblASQtesting INNER JOIN qrylastASQ ON tblASQtesting.ChildID = qrylastASQ.ChildID;
    SELECT [tblASQtesting].[ChildID], Max([tblASQtesting].[DateOfScore]) AS MaxOfDateOfScore
    FROM tblASQtesting
    GROUP BY [tblASQtesting].[ChildID];

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

    Re: query needed to return latest test scores (access2000)

    You should join tblASQTesting and qryLastASQ on ChildID and on the date. The latter is necessary to retrieve only the score from the most recent date. You have joined them only on ChildID. The SQL becomes:

    SELECT tblASQtesting.ChildID, tblASQtesting.ResultID
    FROM tblASQtesting INNER JOIN qrylastASQ ON (tblASQtesting.ChildID = qrylastASQ.ChildID) And (tblASQtesting.DateOfScore = qrylastASQ.MaxOfDateOfScore);

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Florida, USA
    Posts
    155
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: query needed to return latest test scores (access2000)

    that solved it-- appreciate it so much
    now to get this all set up so that I can change it all out for them without disturbing the current data and without stopping their work-- will be so happy if it all works and we get some good data about the need for good day care

Posting Permissions

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