Need a query to return one date and the previous date only

[Tom Travell]

Hi

I have a database which records students and their test scores. Each student could have between two and ten tests. The test are in one table and the students in another.

I want to create a query which will allow me to compare results between tests the student has taken. Unfortunately the database was set up with all the tests having the same name, so the only thing that varies between them is the date and the score.

I want to be able to set a parameter of say "tests in 2009" or "tests between x and y", and have the query return the score for the latest test in that year or period, AND the score for the test before (but not all the tests before!). I will then get the query to calculate the difference so I can measure progress.

I have constructed a query with the parameters but am only able to get it to give me a comparison between all of the tests. I thought about somehow getting a query to order the tests and only return the last two but not sure how to do this.

Any help would be greatly appreciated.

Thanks.

[ADezii]

Tom, kindly post some more info, such as: Name of the Test Table with Field Names and Data Types, Name of the Student Table with Field Names and Data Types. I assume that the Tables are related, if so what are the Linking Field Names? Post some sample Data for a Student and what the results are that you would like to see, then we'll see what we can do.

[Tom Travell]

Hi ADezii

The Student table is called 010t_children, important field names are child_id (an automatically generated unique number), first_name, last_name, dob (dd/mm/yyyy), start_date (dd/mm/yyyy). The Test table is called 020t_test, important field names are test_id (an automatically generated unique number), child_id (linking it to the 010t_children table), read_raw_score (a positive integer relating to their score on the test), read_age_years, read_age_months (these two give the reading age in years and months), spell_raw_score (a positive integer relating to their score on the test), spell_age_years, spell_age_months (again, these give spelling age in years and months), test_date (dd/mm/yyyy), test_type (there are three types of test, initial, progress and Exit - I can quite easily set it up to give me the the difference between two different tests, but the thing I want to be able to do is compare two "progress tests" based on dates, which is more difficult).

Sample data would be: Child_id: 1; first_name:John; last_name:smith; dob:12/10/1998; start date:01/10/2007. They may then have a number of tests such as:

test_id:1; test_type:initial test; test_date:01/10/2007; read_raw_score:20; read_age_years:5; read age months:11; spell_raw_score:15; spell_age_years:5; spell_age_months:8

test_id:47; test_type:progress test; test_date:01/2/2008; read_raw_score:25; read_age_years:6; read age months:5; spell_raw_score:20; spell_age_years:6; spell_age_months:2

test_id:76; test_type:progress test; test_date:12/6/2008; read_raw_score:30; read_age_years:6; read age months:9; spell_raw_score:29; spell_age_years:6; spell_age_months:11

test_id:82; test_type:exit test; test_date:12/6/2009; read_raw_score:35; read_age_years:7; read age months:9; spell_raw_score:37; spell_age_years:7; spell_age_months:11

Result I would like to see is a query which I can set the parameters for. So here for example I would want to put the parameter as "tests between 01/09/2007 and 31/08/2008". I then want the query to return and compare two tests, the latest one in this period and the one immediately before (note, the one before may not always be within the period defined in the parameter).

So here I would expect to see: Child_id:1; first_name:John; test_id:47; test_date:01/02/2008: read_raw_score:25; read_age_years:6; read age months:5; spell_raw_score:20; spell_age_years:6; spell_age_months:2; test_id:76; test_type:progress test; test_date:12/6/2008; read_raw_score:30; read_age_years:6; read age months:9; spell_raw_score:29; spell_age_years:6; spell_age_months:11; (and then some calculated fields, one to calculate the difference in time between the two tests and one to calculate the difference in reading ages between the tests) Date_difference_months:4; read_age_diff_months:4; spell_age_diff_moonths:9.

I hope this is clear. Please let me know if not or let me know if anything needs to be clarified or added.

Thanks,

Tom.

[ADezii]

Give me a little time to digest everything, and see what I can come up with.

Before I can proceed, something immediately jumps up at me:

I then want the query to return and compare two tests, the latest one in this period and the one immediately before (note, the one before may not always be within the period defined in the parameter).

Since the Date 'immediately before' may actually fall 'before' the Start Date Parameter, then this Start Date is actually irrelevant, and the Criteria should be:

<=[End Date]

If you specific Start and End Date Parameters in a Query, then any Dates prior to the Start Date will not be returned, and cannot be analyzed. The answer, as I see it, would be the Single Criteria in conjunction with a defined Sort Order on the Test Dates. If I am incorrect in any of these statements, please clarify. Thanks.
This Thread was quite a challenge, but I do believe that I have arrived at a workable solution which can easily be integrated into your existing Database Structure. Download the Attachment and look over it carefully. Should you have any questions, please post the relevant code, if any, along with as much detail as possible.

[ADezii]

This Thread has been quite a challenge, but I do believe that I have arrived at a workable solution which can easily be integrated into your existing Database Structure. Download the Attachment and look over it carefully. Should you have any questions, please post the relevant code, if any, along with as much detail as possible.