Query problem using "last" (2 Viewers)

[ryetee]

I'm bug fixing a query that is 99.99% correct.
It consists of three tables built using the query design option in access.
The driving table is client order table. This is linked to the client table to pick up client specific data and also a payments table which tracks when payments were made against the order. This obviously can contain many rows to the order. One of the fields on this table is a date due field. This is when the next payment is due. This field is empty when the order has been paid in full.
The idea that the last (chronologically) of the payment rows will contain either the next payment date or spaces to show it's been paid.
The query is basically grouped on order number and the query uses "last" for date of payment with the idea it will, as above, display a date or be blank for payment in full (i.e. no next payment due date).
This seems to work for some orders and not others
For one such that doesn't work it has 3 payment numbers. I'm going to show this with the fields and autonum (I know we shouldn't rely on autonum but just using this to show something I've spotted.

"Autonum"	"Date Next Due"	Client Order Reference
17384	10/1/2026	CLient1OrderX
17385	13/1/2026	CLient1OrderX
17453	Blank	CLient1OrderX

Now with the query using "Last" I was expecting the row with autonum of 17453 to be picked up. In actual fact 17384 is picked up.
Now if I look at the payments table and set the filter to be equal to CLient1OrderX I get the 3 rows above but in the order of
17453
17385
17384
How can I ensure I always pick up the last record

Hope all this makes sense

 

[isladogs]

suggest you show the query design window and query SQL to help with visualisation.

 

[plog]

Actually two sets of sample data is best:

A. Starting data from your tables. Include table/field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect your query to return based on the data you provide in A.

Again, 2 sets of data--starting and expected.

 

[DHookom]

In three decades of using MS Access, I don’t recall ever using last. I always find the max of a date field value in a query to identify the most recent.

 

[Gasman]

In three decades of using MS Access, I don’t recall ever using last. I always find the max of a date field value in a query to identify the most recent.

Unfortunately the o/p is clearing dates when paid.
I have seen previously that last is unreliable, as is first, unless some order is set?

 

[The_Doc_Man]

The ONLY time that LAST works reliably as an aggregate (either in a DLAST - domain qualifier - or SELECT LAST(x) - SQL qualifier) is if you have a query that sorts by some field that uniquely determines the particular order in which something appears. If you have blank or null in some records for this ordering field, at least SOME of those records WILL be out of order or will just not appear at all. In your context, since you admit to having some blank fields, you ARE going to have issues.

The reason is simple: If your field's value for LAST, whatever is being ordered, is not guaranteed, then you run into the problem that LAST doesn't always mean the last date listed on a transaction. It instead could refer to the last time that record was updated. Particularly if more than one record has the same value, the order of LAST is questionable. The aggregate FIRST / DFIRST suffers from the same problem but from the other direction.

 

[arnelgp]

In three decades of using MS Access, I don’t recall ever using last. I always find the max of a date field value in a query to identify the most recent.

Last() will return the Last record you added, While Max() will return the Biggest value in a Field.
suggest you add a TimeStamp field to your table that whenever you Add New, this field is Updated to
the current Date/Time.
then you will only need to Lookup the Max() of this field to retrieve the latest.

 

[LarryE]

I'm bug fixing a query that is 99.99% correct.
It consists of three tables built using the query design option in access.
The driving table is client order table. This is linked to the client table to pick up client specific data and also a payments table which tracks when payments were made against the order. This obviously can contain many rows to the order. One of the fields on this table is a date due field. This is when the next payment is due. This field is empty when the order has been paid in full.
The idea that the last (chronologically) of the payment rows will contain either the next payment date or spaces to show it's been paid.
The query is basically grouped on order number and the query uses "last" for date of payment with the idea it will, as above, display a date or be blank for payment in full (i.e. no next payment due date).
This seems to work for some orders and not others
For one such that doesn't work it has 3 payment numbers. I'm going to show this with the fields and autonum (I know we shouldn't rely on autonum but just using this to show something I've spotted.

"Autonum"	"Date Next Due"	Client Order Reference
17384	10/1/2026	CLient1OrderX
17385	13/1/2026	CLient1OrderX
17453	Blank	CLient1OrderX

Now with the query using "Last" I was expecting the row with autonum of 17453 to be picked up. In actual fact 17384 is picked up.
Now if I look at the payments table and set the filter to be equal to CLient1OrderX I get the 3 rows above but in the order of
17453
17385
17384
How can I ensure I always pick up the last record

Hope all this makes sense

It appears each next payment is 3 days after the last one so create a query that contains the Max Next Due Date then lookup that value and use it as the default value for the Next Due Date field + 3.