Calculating difference between two records

[paddgirl21]

Hi guys,
I have a problem. I have gotten quite far with my Access database with ONE thing left to do. And I have hit a wall.



To give you a background on it:

• The database stores people's testing results
• It has two tables: One with the person's information and another with everyone's test results stored with according to their SSN
• The tables are joined via a relationship of their social security number
• In the test records table, there are multiple fields for each of the different level of tests, including a test date, and the test number. Some people over their lifetime will end up coming in for multiple tests, with varying test dates, test numbers, and test results.

That is the basis of it.
I have made a query with a subquery that will find the duplicates (based on SSN) of the test results and only display those people who have come in before and were tested by a certain company on a certain date. PHEW!

My problem comes when I try to compare a person's recent test results with those of a previous test result. I want to show only those people who have a difference of 10 degrees or more between the two tests.
Example:
Joe came in for two tests in his lifetime. First test revealed the following results: 5,10,5,15
Second test revealed: 20,20,15,15
So, according to my report that I will generate, he should show up on this with an explanation of how much of a difference was between the two tests only if it was a difference of 10 or more.

Test2 Test1, Result
20-5=15
20-10=20
15-5=10
15-15=0

Right now it is displaying all those who have come in on a certain date who were in previously even if there was not a noticable difference.


Here is my current Query that is working good so far:

SELECT TestRecords.SSN, TestRecords.TestDate, TestRecords.L500, TestRecords.L1K, TestRecords.L2K, TestRecords.L3K, TestRecords.L4K, TestRecords.L6K, TestRecords.L8K, TestRecords.TestNumber, EmployeeInfo.LastName, EmployeeInfo.FirstName, EmployeeInfo.MI, EmployeeInfo.DOB, TestRecords.R500, TestRecords.R1K, TestRecords.R2K, TestRecords.R4K, TestRecords.R6K, TestRecords.R8K, TestRecords.R3K

FROM (EmployeeInfo INNER JOIN TestRecords ON EmployeeInfo.SSN = TestRecords.SSN) INNER JOIN [EmployeesByTestDate&Comp] ON EmployeeInfo.SSN = [EmployeesByTestDate&Comp].SSN

WHERE (((TestRecords.SSN) In (SELECT [SSN] FROM [TestRecords] As Tmp GROUP BY [SSN] HAVING Count(*)>1 )))
ORDER BY TestRecords.SSN;


If you need more information, please let me know. I am so stuck right now!! Please someone give me some light into my situation.
Thank you!

 

[Uncle Gizmo]

There are several problems with the approach you are using, I would suggest you read this post here http://www.access-programmers.co.uk/forums/showthread.php?p=671226 first and see if it raises some questions for you.

My advice to you would not to be use the actual SSN number for linking data together but to use a unique identifier normally an auto number field for linking all your data and tables together, not the actual social security number itself.

 

[paddgirl21]

I understand unique identifiers. Either way, that doesn't solve my problem.

 

[paddgirl21]

Right now I am taking an old database from MS DOS and converting it. This is what I have to work with at the moment. When time goes on, I will change the Identifier

 

[jdraw]

You mentioned 2 tables

It has two tables: One with the person's information and another with everyone's test results stored with according to their SSN

but you sql shows 3

(EmployeeInfo INNER JOIN TestRecords ON EmployeeInfo.SSN = TestRecords.SSN) INNER JOIN [EmployeesByTestDate&Comp]

Something does jive. What did I miss?

The query that you are using does NOT do anything to check the TestResults (values) .
It is only looking at those SSNs that have had more than 1 Test.

As Gizmo is suggesting, I think there is a basic structural problem that is adding to complexity. Just my 2 cents...

 

[paddgirl21]

What you think is table 3 is the inner join of another query.