having difficulty with WHERE Clause

[paddgirl21]

I will give you the background of my access database.
It stores information for patients that come in and get their hearing tested. There is an Employee Table and a TestRecords table. So, every employee could have multiple records associated with it in the TestRecords table.
According to OSHA standards, these employees must be tested every year to see if there is a huge shift in their hearing. In order to do this the first time they are tested is called their BASELINE test. Every other test after that is compared to this test.

This is where I am having problems. In my TestRecords table, I have a field named TestNumber so I know which is their BASELINE test (which I have numbered starting at 1 then 1+1 after that). I need to compare their most recent test to their BASELINE (1) and display those records where the results are 10 degrees more than their baseline. I am not sure how to set up my WHERE clause to accomplish this. Any help would be greatly appreciated. Thank you!

 

[jdraw]

Can you give some table info- fields, type -and possibly some sample data?
What have you tried? What results?

 

[paddgirl21]

The Employee table has basic information: LastName, FirstName, SSN(Primary Key), DOB.
The TestRecords table has ID(Primary Key Access keeps track), SSN, TestDate, TestResults (which is 14 fields Right and Left ear results from different dB testing levels), and TestNumber.

The data for the testresults are all just basic numbers (eg: 5, 10, 15 all the way to 100, in intervals of 5)

As it is right now, I have been able to find those records who had testing done on a certain date (Enter test date

I want these individuals that were tested on this date to have their testresults compared.

This is an example of two different test results for the same person taken at two different times (as evidence by TestDate) in the attached Excel sheet

 

[paddgirl21]

I still have not figured this out as of yet. I have gotten further with the database. i reposted this under a different thread about comparing two records in the database