JOIN Query to return most recent record

[dbay]

I have two tables with a one to many relationship. The tables are linked by the INDEX column.

EXAMPLE:

TABLE_1
INDEX       NAME
1          Name_A
2          Name_B
3          Name_C

TABLE 2
INDEX        NUM_INDEX     STATUS
1               1          REJECTED
1               2          REJECTED
1               3          OPEN
2               1          CLOSED
3               1          REJECTED
3               2          OPEN

I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).

Example:
Name_A, OPEN
Name_B, CLOSED
Name_C, OPEN

SQL that I have now.

SELECT A.FIN_Finding_Number, B.Max_Index
FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index
FROM TBL_Response GROUP BY RES_Finding_Index )  AS B ON A.FIN_Finding_Index = B.RES_Finding_Index
WHERE (((A.FIN_Finding_Index)=34));

This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.

This has got to be something extremely simple for experienced Access users.

 

[plog]

First, when you genericize your table and field names in your explanation and then post the actual names of them in SQL as well, you are only confusing us. Be consitent in your names. I think TABLE 2 is TBL_Response and NUM_INDEX is RES_Response_Index, but I'm not certain.


Now in your SQL I see a subquery that is getting the NUM_INDEX you want for each INDEX. What you need to do is make that an actual query in your database. Copy out that code, paste it in a new query and name it something like 'subStatus'. Then to get the final results you want, you need to make another query using TABLE_1 and linking subStatus to it via the INDEX field in each, and you would then link TABLE 2 to subStatus by both the INDEX field and by the NUM_INDEX field in TABLE 2 to the MaxOfNUM_INDEX field in subStatus.

Hope that made sense, but like I said, when you use 2 different naming schemas its hard to follow.

 

[dbay]

First, when you genericize your table and field names in your explanation and then post the actual names of them in SQL as well, you are only confusing us. Be consitent in your names. I think TABLE 2 is TBL_Response and NUM_INDEX is RES_Response_Index, but I'm not certain.


Now in your SQL I see a subquery that is getting the NUM_INDEX you want for each INDEX. What you need to do is make that an actual query in your database. Copy out that code, paste it in a new query and name it something like 'subStatus'. Then to get the final results you want, you need to make another query using TABLE_1 and linking subStatus to it via the INDEX field in each, and you would then link TABLE 2 to subStatus by both the INDEX field and by the NUM_INDEX field in TABLE 2 to the MaxOfNUM_INDEX field in subStatus.

Hope that made sense, but like I said, when you use 2 different naming schemas its hard to follow.

Thank you for your reply.

The working SQL:

SELECT TBL_FINDINGS.FIN_Finding_Index, TBL_FINDINGS.FIN_Finding_Number, TBL_RESPONSE.RES_Response_Status
FROM (TBL_FINDINGS INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) AS Max_Index
FROM TBL_RESPONSE
GROUP BY RES_Finding_Index )  AS A ON TBL_FINDINGS.FIN_Finding_Index = A.RES_Finding_Index) INNER JOIN TBL_RESPONSE ON A.Max_Index = TBL_RESPONSE.RES_Response_Index
WHERE (((TBL_FINDINGS.FIN_Audit_Index)=48))
ORDER BY TBL_FINDINGS.FIN_Finding_Number;