Query to only show last entry...

[rhett7660]

Hello..

I have a one applicant to many callers database setup. I want to be able to see only the last call to the applicant based on date.

I have tried the following:

SELECT tblApplicant.ApplicantID, tblApplicant.ApplicantLName, tblApplicant.ApplicantFName, tblApplicant.LastFour, tblApplicant.TestDate, tblApplicant.JacketExpireDate, tblApplicant.ContinueProcessDate, [ApplicantLName] & ", " & [ApplicantFName] AS Name, tblYsNo.YesNoType, tblLuTestType.Type, tblCaller.Comment, tblCaller.Date
FROM tblYsNo INNER JOIN ((tblApplicant INNER JOIN tblLuTestType ON tblApplicant.TestType = tblLuTestType.TestType) INNER JOIN tblCaller ON tblApplicant.ApplicantID = tblCaller.ApplicantID) ON tblYsNo.YesNoMain = tblApplicant.Interested
WHERE (((tblYsNo.YesNoType)=[Forms]![frmReport]![cboInterest]) AND ((tblLuTestType.Type)=[Forms]![frmReport]![cmdTestType]))
ORDER BY tblApplicant.ApplicantLName, tblCaller.Comment DESC , tblCaller.Date DESC;

Which had both the caller date and coller comment descending and I have also had the caller date and coller comment ascending. Then I have tried one at a time. But in the report that is based off of this query it still only shows the first comment by date not the last comment.

IE

01/01/2004 comment this is a test
01/02/2004 comment this is a test again
01/03/2004 comment test test

On the report I only want to see the 01/03/2004 comment not the 01/01/2004 or 01/02/2004.

Does this make sense? I have been banging my head...

 

[boblarson]

Have you tried creating a query sorted in Descending order and selecting the Top 1

Or

creating a query and using the Max grouping on the date

 

[rhett7660]

Creating a query sorted in descendeing order.....

No I haven't..... I didn't even think to make the whole query descending........ dang that is a good idea.. trying it now..

 

[rhett7660]

Bob.. I put the max on the date... and now the date doesn't show up but the comment I want to show up, shows up now. And I am gettig a promt now for a date now too....

Here is the sql:

SELECT tblApplicant.ApplicantID, tblApplicant.ApplicantLName, tblApplicant.ApplicantFName, tblApplicant.LastFour, tblApplicant.TestDate, tblApplicant.JacketExpireDate, tblApplicant.Comment, tblApplicant.HomePhone, tblApplicant.CellPhone, tblApplicant.WorkPhone, tblApplicant.ContinueProcessDate, [ApplicantLName] & ", " & [ApplicantFName] AS Name, tblYsNo.YesNoType, tblLuTestType.Type, Max(tblCaller.Date) AS MaxOfDate, tblCaller.Comment
FROM tblYsNo INNER JOIN ((tblApplicant INNER JOIN tblLuTestType ON tblApplicant.TestType = tblLuTestType.TestType) INNER JOIN tblCaller ON tblApplicant.ApplicantID = tblCaller.ApplicantID) ON tblYsNo.YesNoMain = tblApplicant.Interested
GROUP BY tblApplicant.ApplicantID, tblApplicant.ApplicantLName, tblApplicant.ApplicantFName, tblApplicant.LastFour, tblApplicant.TestDate, tblApplicant.JacketExpireDate, tblApplicant.Comment, tblApplicant.HomePhone, tblApplicant.CellPhone, tblApplicant.WorkPhone, tblApplicant.ContinueProcessDate, [ApplicantLName] & ", " & [ApplicantFName], tblYsNo.YesNoType, tblLuTestType.Type, tblCaller.Comment
HAVING (((tblApplicant.TestDate) Between [Forms]![frmReport]![txtStartInt] And [Forms]![frmReport]![txtEndInt]) AND ((tblYsNo.YesNoType)=[Forms]![frmReport]![cboInterest]) AND ((tblLuTestType.Type)=[Forms]![frmReport]![cboTestType]))
ORDER BY tblApplicant.ApplicantLName;

 

[Brianwarnock]

I'm going to guess here that you are running the report not just the query and that you have a field in the report referring to tblcaller.Date, but now you do not select that in the query, change the report field source to MaxofDate, you will now get the date and not be prompted for a date.

Just a guess mind.

BTW never use Date as a field name it is an ACCESS function and using ACCESS reserved words will come back and bite you.

Brian

 

[Brianwarnock]

Rhett has not come back on this, it may be useful for other forum searchers to know what was the problem.

Brian

 

[rhett7660]

Hi Brian...

Still working on it.... sort of...... I forgot about the date thing dang it..... When I typed it in to.. I had this nagging feeling something wasn't right....