First and last function query filtering issues (1 Viewer)

[pravistar]

Hi,

I am facing issues filtering data with data from a table. My table details are as below


ClaimNumber: Short text
ClaimDate : Date
PaymentDate : Date/time
Part : $ Currency
Labor: $ Currency
Misc : $ Currency
Total : $ Currency.

ClaimType : Short Text

As Claim payments normally coming as installments, the claim number keeps repeating in the table. 1st payment came on 01.06.2020 and then the second payment on 08.06.2020 like that.
I want to make a query that can retrieve the data with respect to the last payment date against each claim. I have used Last and max functions. For some records output for the query is correct but for some not. Awaiting your feedback.

Thanks
Praveen

 

[arnelgp]

use the Max() function in your query:

select ClaimNumber, Max(ClaimDate) As MaxOfClaimDate From yourTable Group By ClaimNumber;

 

[Pat Hartman]

First and Last are 100% dependent on the order of a recordset and if you haven't sorted the recordset using a query, you cannot ever rely on consistency. arne's suggestion of finding the Max() date for a claim will find the "last" payment" and using Min() for the date will find the "first" payment.

Always use Min/Max rather than First/Last when that is what you actually want.

In a relational database (not just Access), tables and queries are unordered sets so First and Last have no meaning.