Problem with a Sub Query 1

[itmasterw]

Hi,
I am trying to get a SUB Query to Show only loans with the Max Date. Here are the loans woith the original data ( Sorry that htis is meesed up but I did not know how to get this in here other wise).

LoanNum Date LoanBalance Borrower Name Bowwer Address BowwerCity Bowwer State Bowwer Zip
123 02-Jan-05 123.25 Jim Jones 12 Bars ave. Tonawanda NY 14228
123 15-Jan-05 1256.36 Jim Jones 12 Bars ave. Tonawanda NY 14228
123 12-Feb-05 125326.23 Jim Jones 12 Bars ave. Tonawanda NY 14228
124 01-Feb-05 78985.12 Bill Gates 14 Tennis Ln Buffalol NY 12456
124 15-Feb-05 256.36 Bill Gates 14 Tennis Ln Buffalol NY 12456
124 05-Feb-05 235.36 Bill Gates 14 Tennis Ln Buffalol NY 12456

I want to get it to this:
LoanNum MaxOfDate LoanBalance Borrower Name Bower Address Bower City Bower State Bowwer Zip
123 2/12/2005 125326.23 Jim Jones 12 Bars ave. Tonawanda NY 14228
124 2/15/2005 256.36 Bill Gates 14 Tennis Ln Buffalol NY 12456

This is my suub query that I have so far but it keeps wanting me to use the key word EXISTS , but if I do it doesnot work; it comes back Syntext error.

SELECT MaxDate.LoanNum, MaxDate.Date, MaxDate.LoanBalance, MaxDate.[Borrower Name], MaxDate.[Bowwer Address], MaxDate.BowwerCity, MaxDate.[Bowwer State], MaxDate.[Bowwer Zip]
FROM MaxDate
Where MaxDate.Date IN(SELECT MaxDate.LoanNum, Max(MaxDate.Date) From MaxDate);

Note: I can get the results I need by finding the max date loans by them selves and making a table of this. Then do ing a join between this table and the original table, I am looking for a more direct way.
Any help would be appreaciated.
Thank you

 

[lespaul]

I can get the results I need by finding the max date loans by them selves and making a table of this

You don't actually have to make a table to do this, you can create a query that gets the loans and the max date and then join into that query. In older versions of Access this is the only way to accomplish what you are doing. In newer versions, you can put the query in a SELECT statement in the INNER JOIN (as shown below):

SELECT A.LoanNum, B.Date, A.LoanBalance, A.[Borrower Name], A.[Bowwer Address], A.BowwerCity, A.[Bowwer State], A.[Bowwer Zip]
FROM [b]TableName[/b] A
INNER JOIN (SELECT LoanNum, Max([Date]) As MaxDate FROM [b]TableName[/b] GROUP BY LoanNum) B ON A.LoanNum = B.LoanNum

Leslie

 

[itmasterw]

Thnaks for geting back to me, but I am not sure I am uderstanding it totaly. I took it that you ment a self join, so I did this
SELECT A.LoanNum, B.Date, A.LoanBalance, A.[Borrower Name], A.[Bowwer Address], A.BowwerCity, A.[Bowwer State], A.[Bowwer Zip]
FROM MaxDate A
INNER JOIN (SELECT LoanNum, Max([Date]) As MaxDate FROM MaxDate GROUP BY LoanNum) B ON A.LoanNum = B.LoanNum
But it came back Syntax error in From clause.
I like what you have here buit some how I am messing it up. any ideas what I am doing wrong?

 

[itmasterw]

I am sorry I did not get the first part of your statement. I am using Access 97 unfortuantly is this too old?
Again thanks for your help.

 

[lespaul]

If you are using Access 97 you will need two queries:

save this query (qryMax):

SELECT LoanNum, Max([Date]) As MaxDate FROM TableName GROUP BY LoanNum

then run your second query:

SELECT A.LoanNum, B.MaxDate, A.LoanBalance, A.[Borrower Name], A.[Bowwer Address], A.BowwerCity, A.[Bowwer State], A.[Bowwer Zip]
FROM TableName A
INNER JOIN ([b]qryMax[/b]) B ON A.LoanNum = B.LoanNum

Leslie

 

[itmasterw]

Thanks for your help

 

[lespaul]

no prob! thanks for the star!

les