Access 2000 Newby needs help with Most Recent Date 2

[tiredmom911]

Hi, I apologize for asking a questiong that seems to have been answered here previously, but in SQL form. It has been a long time since I've done anything in Access though so I need help.

I have two tables, a main employee table with the primary key being Employee ID and an immunization table with the primary key being autonumber.

In the immunization table we are tracking when an employee gets their TB skin test. Some employees have several dates as they get tested every year.

I would like to have a query where each employee record (from main table) shows the most recent TB test given (from the immunization table). Not being conversant with SQL I did it the "beginner" way and designed a query using Employee ID, the other fields in the main table and then I added Max([Given]) to get the most recent date. The result was good, each employee had the most recent date of their test.

However, when I tried to add more fields from the secondary immunization table (like the result of the test) it screws things up and I get all test dates for each employee.

What the heck am I doing wrong? Remember, I am a SQL dummy.

 

[lespaul]

Typed, not tested: switch to SQL view of the query and paste. Fix the bolded table & field names

SELECT * FROM [b]immunizations[/b] I
INNER JOIN (SELECT [b]EmployeeID[/b], Max([b]DateTestGiven[/b]) As LastTest FROM Immunizations GROUP BY [b]EmployeeID[/b]) D ON I.[b]EmployeeID[/b] = D.[b]EmployeeID[/b] and I.[b]DateTestGiven[/b] = D.LastTest

Leslie

Have you met Hardy Heron?

 

[PHV]

The very same question has a reply here:
thread701-1534482

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[tiredmom911]

Thank you so much for responding. I am getting "Syntax error in From clause" message. This is what I used: SELECT * FROM Historical TB Data I
INNER JOIN (SELECT EE#, MAX(Given) As LastTest FROM Historical TB Data GROUP BY EE#) D ON I.EE# = D.EE# and I.Given = LastTest

Any idea where I messed up??

 

[lespaul]

SELECT * FROM [Historical TB Data] I
INNER JOIN (SELECT [EE#], MAX(Given) As LastTest FROM [Historical TB Data] GROUP BY [EE#]) D ON I.[EE#] = D.[EE#] and I.Given = D.LastTest

Leslie

Have you met Hardy Heron?

 

[tiredmom911]

Let me ask the question a different way. I can run a query that gives me the most recent date of TB test - SQL is as follows: SELECT [Main EE Data 3-2-09].RC, [Main EE Data 3-2-09].Org, [Main EE Data 3-2-09].EmpNum, [Main EE Data 3-2-09].EmpName, [Main EE Data 3-2-09].PosTitle, [Main EE Data 3-2-09].OccupFTE, Max([Historical TB Data].Given) AS MaxOfGiven
FROM [Main EE Data 3-2-09] INNER JOIN [Historical TB Data] ON [Main EE Data 3-2-09].EmpNum = [Historical TB Data].[EE#]
GROUP BY [Main EE Data 3-2-09].RC, [Main EE Data 3-2-09].Org, [Main EE Data 3-2-09].EmpNum, [Main EE Data 3-2-09].EmpName, [Main EE Data 3-2-09].PosTitle, [Main EE Data 3-2-09].OccupFTE
ORDER BY [Main EE Data 3-2-09].EmpName

However, if I try to add in the design grid the Lot, Result or any other data from the Historical TB Data table the results returned do not pare down by latest test.

I so appreciate the help already given and ask forgiveness for being obtuse. Please help this idiot!

 

[PHV]

Again:
thread701-1534482

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lespaul]

ok here's the deal, you have to first get all the records you want and extra fields you want and then join into that result set to get the other information you want, so maybe this (depending on your version of access you may not be able to do it this way! - older version you have to save the first query and use the saved name in the second)....

So try this:

This query will get, for each EE#, the last date and the related information:

SELECT [EE#], Max(Given) As LastGiven, Lot, Result, OtherHistoricalData FROM [Historical TB Data] GROUP BY [EE#], Lot, Result, OtherHistoricalData

If you join THAT into your main table you should get what you want:

SELECT M.RC, M.Org, M.EmpNum, M.EmpName, M.PosTitle, M.OccupFTE, A.LastGiven, A.Lot, A.Result
FROM  [Main EE Data 3-2-09] M
INNER JOIN (SELECT [EE#], Max(Given) As LastGiven, Lot, Result, OtherHistoricalData FROM [Historical TB Data] GROUP BY [EE#], Lot, Result, OtherHistoricalData) A ON M.EmpNum = A.[EE#]
order by EmpName

HTH

Leslie

Have you met Hardy Heron?