Extract Latest Date using MS Access select query 2

[BxWill]

Using several make table queries based on passthrough queries in MS Access Db to extract records with the latest reviewer end date.

Upon trying to run the query below within MS Access to extract records with the latest "REVIEWER_END_DT", I receive an error message related to the FROM clause.

Note, "REVIEW_MGMT" is an oracle table and "tblReport" is the local MS Access table that is a result of a make table query sourced from a passthrough query.

Have tried placing the comma in different places but haven't been successful.

Any insight as to the problem and a solution is greatly appreciated.

SELECT tblReport.LastName, tblReport.FirstName, tblReport.AcctNo, A.REVIEWER, A.REVIEWER_END_DT
FROM REVIEW_MGMT as A
INNER JOIN (Select T.PRODUCT_CD, Max(A.REVIEWER_END_DT) AS MaxReviewEndDt
From CUSTOMER as T
GROUP BY T.PRODUCT_CD AS TMP
ON T.PRODUCT_CD = TMP.PRODUCT_CD
AND A.PRODUCT_CD = T.PRODUCT_CD)
INNER JOIN tblReport
ON T.REV_NO = tblReport.AcctNo;

 

[lespaul]

i think your parens are in the wrong place on your SELECT JOIN and you're not joining on the max date either. Maybe this:

[tt]
SELECT tblReport.LastName, tblReport.FirstName, tblReport.AcctNo, A.REVIEWER, A.REVIEWER_END_DT
FROM REVIEW_MGMT as A
INNER JOIN (Select T.PRODUCT_CD, Max(A.REVIEWER_END_DT) AS MaxReviewEndDt
From CUSTOMER GROUP BY T.PRODUCT_CD AS TMP) as T
ON T.PRODUCT_CD = TMP.PRODUCT_CD
AND A.PRODUCT_CD = T.PRODUCT_CD
AND A.REVIEWER_END_DT = T.MazReviewEndDt
INNER JOIN tblReport
ON T.REV_NO = tblReport.AcctNo; [/tt]

Leslie

 

[BxWill]

Will try the suggestion.

Also, is it possible to just enter something in the "Criteria" section like "=(SELECT (A.REVIEWER_END_DT) FROM REVIEW_MGMT WHERE A.REVIEWER_END_DT = (SELECT MAX(A.REVIEWER_END_DT)"

 

[BxWill]

Did try the suggestion and now the error is "syntax error in the JOIN clause"

What am I missing here?

SELECT tblReport.LastName, tblReport.FirstName, tblReport.AccountNo, CUSTOMER.ReviewerName, CUSTOMER.REVIEWER_END_DT
FROM CUSTOMER as A
INNER JOIN (SELECT T.AccountNo, MAX(A.REVIEWER_END_DT) AS MaxReviewerEndDt
FROM CUSTOMER
GROUP BY T.AccountNo AS TMP) as T
ON T.AccountNo = TMP.AccountNo
AND A.AccountNo = T.AccountNo
AND A.REVIEWER_END_DT = T.MaxReviewerEndDt
INNER JOIN MEMBER
ON CUSTOMER.CustomerID = MEMBER.CustomerID
INNER JOIN tblReport
ON MEMBER.AccountNo = tblReport.AccountNo;

 

[lespaul]

maybe this....i didn't see the TMP alias before:
[tt]
SELECT tblReport.LastName, tblReport.FirstName, tblReport.AccountNo, A.ReviewerName, A.REVIEWER_END_DT
FROM CUSTOMER as A
INNER JOIN (SELECT AccountNo, MAX(REVIEWER_END_DT) AS MaxReviewerEndDt
FROM CUSTOMER
GROUP BY AccountNo) as T
ON A.AccountNo = T.AccountNo
AND A.REVIEWER_END_DT = T.MaxReviewerEndDt
INNER JOIN MEMBER
ON A.CustomerID = MEMBER.CustomerID
INNER JOIN tblReport
ON MEMBER.AccountNo = tblReport.AccountNo;[/tt]

Leslie

 

[BxWill]

Thanks for the patience.

Still have not been successful.

Therefore, posting the query that extracts multiple records per member's name - records with the same LastName, FirstName, AccountNo, etc. but different Review End Dates.

What modifications are needed to extract the record with the latest ReviewEndDt?


SELECT tblReport.LastName, tblReport.FirstName, tblReport.AccountNo,
tblReviewMgmt.ReviewerName, tblReviewMgmt.ReviewEndDt
FROM (tblReviewMgmt INNER JOIN tblMember ON tblReviewMgmt.MbrCode = tblMember.MbrCode)

INNER JOIN tblReport ON tblMember.AccountNo = tblReport.AccountNo;

 

[PHV]

I'd try this:

SELECT R.LastName, R.FirstName, R.AccountNo, V.ReviewerName, V.ReviewEndDt
FROM ((tblReviewMgmt V
INNER JOIN tblMember M ON V.MbrCode = M.MbrCode) 
INNER JOIN tblReport R ON M.AccountNo = R.AccountNo)
INNER JOIN (SELECT MbrCode, Max(ReviewEndDt) AS LastDate FROM tblReviewMgmt GROUP BY MbrCode
) L ON V.MbrCode = L.MbrCode AND V.ReviewEndDt = L.LastDate

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

 

[BxWill]

Was able to extract query results but multiple records (records with the same member name but different Review End dates) were still displayed.

Therefore, entered "Distinct" next to "SELECT" in the query and currently running. Over 15 minutes and query is still processing! Maybe, querying a Oracle table and a local MS Access table using "Distinct" is not the preferred method.

Another option is to create a make table query and initially store the query results in a local MS Access table that contain duplicate records and then create a query of this table to extract the final results - one record per member with the latest Review End Date.

Any further thoughts?

 

[BxWill]

The query with "distinct" finally completed and there are still some duplicate records - records with different Reviewer Name that have the same Review End Date (more than likely due to issues with the loading of the data into the Oracle table) and records with different Reviewer Name and different Review End Date.

Is it possible to modify the subquery section to select the account code from table "R" as well as the Review End date from the "V" table and then group on the account code?

I believe that this will allow the display of just one record per account code - the record with the latest Review End Date.