Max(Date) gets error about EXIST reserved word 1

[solun]

I know this type of question gets answered a lot, and I've tried several of the various answers... just when i think i'm making progress, something else breaks!

I'm trying to write a SQL statement that retrives several fields from a record that is selected because its date is the highest of several options. In English: Get the set of applications related to Loan17. Find the one with the last date. Get several fields from that record.

SELECT A.*
FROM LoanDetailsApplications AS A
WHERE A.ApplicationID=
(SELECT ApplicationID, Max(B.DateApp)
FROM LoanDetailsApplications AS B
WHERE B.LoanID=17);

So, what am I doing wrong?

Thanks much,

Heidi


Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[HarleyQuinn]

How about:

SELECT A.*
FROM LoanDetailsApplications A, (SELECT ApplicationID, Max(B.DateApp) as Dates
FROM LoanDetailsApplications B  
WHERE B.LoanID=17) MaxAppDet
WHERE A.ApplicationID = MaxAppDet.ApplicationID and A.DateApp = MaxAppDet.Dates

Hope this helps


Harleyquinn

---------------------------------
For tsunami relief donations

 

[HarleyQuinn]

The reason you got the error about EXISTS in your original query was that you were trying to do the =(SELECT... on a query that returned multiple rows (i.e. Access doesn't know which row you want to match against). Doing it the way you were only allows you to return one row in the second SELECT query.

Hope this helps

Harleyquinn

---------------------------------
For tsunami relief donations

 

[PHV]

SELECT A.*
FROM LoanDetailsApplications AS A INNER JOIN (
SELECT ApplicationID, Max(DateApp) AS LastDate FROM LoanDetailsApplications WHERE LoanID=17 GROUP BY ApplicationID
) AS B ON A.ApplicationID = B.ApplicationID AND A.DateApp = B.LastDate
WHERE A.LoanID=17;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[HarleyQuinn]

PHV - thanks for posting a SQL statement that would actually work. I created mine by editing the OP's statement and posted it before I realised that I had made made a blindly obvious mistake, forgot to add the GROUP BY on the subquery .

Cheers

Harleyquinn

---------------------------------
For tsunami relief donations

 

[solun]

I just tested PH's version and came up with all the loanid=17 records, not just the one with the max(DateApp)...


SELECT A.*
FROM LoanDetailsApplications AS A INNER JOIN (
SELECT ApplicationID, Max(DateApp) AS LastDate FROM LoanDetailsApplications WHERE LoanID=17 GROUP BY ApplicationID
) AS B ON A.ApplicationID = B.ApplicationID AND A.DateApp = B.LastDate
WHERE A.LoanID=17;

results:
ApplicationID LoanID DateApp Amount
46 17 3/28/2004 $62,000.00
47 17 4/21/2004 $65,000.00
72 17 3/28/2004 $62,000.00



Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[PHV]

SELECT *
FROM LoanDetailsApplications
WHERE LoanID=17 AND DateApp = (SELECT Max(DateApp) AS LastDate FROM LoanDetailsApplications WHERE LoanID=17);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[solun]

Yes, beautiful! Thank you, oh great PHV!

And thanks to Harleyquinn for the explanation

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com