Query max value of Followupdate

[starclassic]

Hello everyone,

How do I query only the max date to show up on the database with 2 or more contacts.
I have this query but it will show all record regardless of the follow update. I only need to show the up to date follow up date not the previous date. Any help will do.
I try the Max(FollowUpdate) but getting aggregate error.

Thanks

SELECT tblContact.MessageType, tblContact.FollowUpDate, tblCollectionsData.ContactMain, tblCollectionsData.AFOCode, tblCollectionsData.AgentCode, tblCollectionsData.AgentName, tblCollectionsData.PremCancelDate, tblCollectionsData.CancelDate, tblCollectionsData.PolicyEffectiveDate, tblCollectionsData.PolicyTerm, tblCollectionsData.ProRateDays, tblCollectionsData.ProRate, tblCollectionsData.Premium, tblCollectionsData.ProRateAmount, tblCollectionsData.TransAmount, tblCollectionsData.SystemCalculateAmountOwing, tblCollectionsData.LName, tblCollectionsData.Address, tblCollectionsData.Address1, tblCollectionsData.Address2, tblCollectionsData.Address3, tblCollectionsData.City, tblCollectionsData.Province, tblCollectionsData.PostalCode, tblCollectionsData.HomeNumber, tblCollectionsData.BusinessNumber, tblCollectionsData.Year, tblCollectionsData.Make, tblCollectionsData.Model, tblCollectionsData.Style, tblCollectionsData.Vin, tblCollectionsData.Class, tblCollectionsData.PolicyType, tblCollectionsData.FName, tblCollectionsData.DateUpLoad, tblCollectionsData.PremCancelDate1, tblCollectionsData.Overdue, tblCollectionsData.Closed, tblCollectionsData.CellNumber, tblContact.ContactID, tblContact.Contact, tblContact.ByLetter, tblContact.ccAgent, tblContact.EmployeeAlias, tblContact.ByPhone, tblContact.LeftMessage, tblContact.Promise, tblContact.Other, tblContact.OtherExplain, tblContact.GComments, tblContact.PolicyNumber, tblContact.Message, tblContact.MessageType, tblContact.ContactNumber
FROM tblCollectionsData RIGHT JOIN tblContact ON tblCollectionsData.PolicyNumber = tblContact.PolicyNumber
WHERE (((tblContact.FollowUpDate) Between [Forms]![frmQueue]![txtMoreThanTwo] And [Forms]![frmQueue]![txtMoreThanTwoEnd]) AND ((tblCollectionsData.ContactMain)>="3") AND ((tblCollectionsData.Closed)=0) AND ((tblContact.ContactNumber)="3"))
ORDER BY tblCollectionsData.LName, tblContact.ContactID DESC , tblContact.ContactNumber DESC;

 

[HarleyQuinn]

You can still use Max(FollowUpdate), but you will need to add a GROUP BY clause for all of the other fields you want to show e.g.

SELECT tblContact.MessageType, tblContact.FollowUpDate, tblCollectionsData.ContactMain, tblCollectionsData.AFOCode, tblCollectionsData.AgentCode, tblCollectionsData.AgentName, tblCollectionsData.PremCancelDate, tblCollectionsData.CancelDate, tblCollectionsData.PolicyEffectiveDate, tblCollectionsData.PolicyTerm, tblCollectionsData.ProRateDays, tblCollectionsData.ProRate, tblCollectionsData.Premium, tblCollectionsData.ProRateAmount, tblCollectionsData.TransAmount, tblCollectionsData.SystemCalculateAmountOwing, tblCollectionsData.LName, tblCollectionsData.Address, tblCollectionsData.Address1, tblCollectionsData.Address2, tblCollectionsData.Address3, tblCollectionsData.City, tblCollectionsData.Province, tblCollectionsData.PostalCode, tblCollectionsData.HomeNumber, tblCollectionsData.BusinessNumber, tblCollectionsData.Year, tblCollectionsData.Make, tblCollectionsData.Model, tblCollectionsData.Style, tblCollectionsData.Vin, tblCollectionsData.Class, tblCollectionsData.PolicyType, tblCollectionsData.FName, tblCollectionsData.DateUpLoad, tblCollectionsData.PremCancelDate1, tblCollectionsData.Overdue, tblCollectionsData.Closed, tblCollectionsData.CellNumber, tblContact.ContactID, tblContact.Contact, tblContact.ByLetter, tblContact.ccAgent, tblContact.EmployeeAlias, tblContact.ByPhone, tblContact.LeftMessage, tblContact.Promise, tblContact.Other, tblContact.OtherExplain, tblContact.GComments, tblContact.PolicyNumber, tblContact.Message, tblContact.MessageType, tblContact.ContactNumber,
Max(FollowUpdate) AS MaxDate
FROM tblCollectionsData RIGHT JOIN tblContact ON tblCollectionsData.PolicyNumber = tblContact.PolicyNumber
WHERE (((tblContact.FollowUpDate) Between [Forms]![frmQueue]![txtMoreThanTwo] And [Forms]![frmQueue]![txtMoreThanTwoEnd]) AND ((tblCollectionsData.ContactMain)>="3") AND ((tblCollectionsData.Closed)=0) AND ((tblContact.ContactNumber)="3"))
GROUP BY tblContact.MessageType, tblContact.FollowUpDate, tblCollectionsData.ContactMain, tblCollectionsData.AFOCode, tblCollectionsData.AgentCode, tblCollectionsData.AgentName, tblCollectionsData.PremCancelDate, tblCollectionsData.CancelDate, tblCollectionsData.PolicyEffectiveDate, tblCollectionsData.PolicyTerm, tblCollectionsData.ProRateDays, tblCollectionsData.ProRate, tblCollectionsData.Premium, tblCollectionsData.ProRateAmount, tblCollectionsData.TransAmount, tblCollectionsData.SystemCalculateAmountOwing, tblCollectionsData.LName, tblCollectionsData.Address, tblCollectionsData.Address1, tblCollectionsData.Address2, tblCollectionsData.Address3, tblCollectionsData.City, tblCollectionsData.Province, tblCollectionsData.PostalCode, tblCollectionsData.HomeNumber, tblCollectionsData.BusinessNumber, tblCollectionsData.Year, tblCollectionsData.Make, tblCollectionsData.Model, tblCollectionsData.Style, tblCollectionsData.Vin, tblCollectionsData.Class, tblCollectionsData.PolicyType, tblCollectionsData.FName, tblCollectionsData.DateUpLoad, tblCollectionsData.PremCancelDate1, tblCollectionsData.Overdue, tblCollectionsData.Closed, tblCollectionsData.CellNumber, tblContact.ContactID, tblContact.Contact, tblContact.ByLetter, tblContact.ccAgent, tblContact.EmployeeAlias, tblContact.ByPhone, tblContact.LeftMessage, tblContact.Promise, tblContact.Other, tblContact.OtherExplain, tblContact.GComments, tblContact.PolicyNumber, tblContact.Message, tblContact.MessageType, tblContact.ContactNumber
ORDER BY tblCollectionsData.LName, tblContact.ContactID DESC , tblContact.ContactNumber DESC;

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

What is the PrimaryKey of tblContact ?

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

 

[PHV]

OOps, wrong question I guess.
You want the latest FollowUpdate for each ContactID ?

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

 

[HarleyQuinn]

And mine won't currently work (though it should give you aqn idea of how to create queries that use aggregate functions) as I've included FollowUpDate twice in the SELECT (once is the Max(), you can just remove the first one) and it's also in the GROUP BY (again, copied the SELECT list ), if you just remove that too, it should be fine.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.