Query problem using "last" (5 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.

 

[GPGeorge]

I'm bug fixing a query that is 99.99% correct.

Spoiler:

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

While I agree with Colin that sample data and the actual SQL from the query can be highly useful context from which a proposal can be derived, others have pointed out the real culprit in this scenario, trying to use Last in a query. I won't belabor the point made so well by others.

Use a sort order that reliably puts your records in the sequence you need. Use Max() or Min() to select the largest or smallest value in that sequence.

 

[Ken Sheridan]

The following query is from a clinical database in which the PatientTreatmentProgramme table includes a DateAdministered column. The query is analogous to what you are attempting in that, for each treatment per patient it returns a row where the date administered is the latest date and there is no Null at the DateAdministered column position, or the DateAdministered column position is Null:

SELECT
    Patients.PatientID,
    Patients.FirstName,
    Patients.LastName,
    Treatments.Treatment,
    PTP1.DateScheduled,
    PTP1.DateAdministered
FROM
    (
        Treatments
        INNER JOIN (
            Patients
            INNER JOIN PatientTreatments ON Patients.PatientID = PatientTreatments.PatientID
        ) ON Treatments.TreatmentID = PatientTreatments.TreatmentID
    )
    INNER JOIN PatientTreatmentProgramme AS PTP1 ON (PatientTreatments.StartDate = PTP1.StartDate)
    AND (PatientTreatments.TreatmentID = PTP1.TreatmentID)
    AND (PatientTreatments.PatientID = PTP1.PatientID)
WHERE
    PTP1.DateAdministered IS NULL
    OR (
        PTP1.DateAdministered = (
            SELECT
                MAX(DateAdministered)
            FROM
                PatientTreatmentProgramme AS PTP2
            WHERE
                PTP2.PatientID = Patients.PatientID
                AND PTP2.TreatmentID = PTP1.TreatmentID
                AND NOT EXISTS (
                    SELECT
                        *
                    FROM
                        PatientTreatmentProgramme AS PTP3
                    WHERE
                        PTP3.PatientID = PTP2.PatientID
                        AND PTP3.TreatmentID = PTP2.TreatmentID
                        AND PTP3.DateAdministered IS NULL
                )
        )
    );

 

[Isaac]

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.

Me neither. use dmax/dmin or better yet, sql to select the top 1

 

[MajP]

One thing that has been said here is that First() and Last() will return the first record added or the last record added.
That is definitely not true and not guaranteed. You might get lucky, but likely you may not. So you cannot count on these and therefore they should never be used for writing queries where you want the first added or last added record.

According to MS

These functions return the value of a specified field in the first or last record, respectively, of the result set returned by a query. If the query does not include an ORDER BY clause, the values returned by these functions will be arbitrary because records are usually returned in no particular order.

Nowhere does it suggest this is the first or last record entered.

These are legacy JET functions and not part of ANSI SQL
Access tables have no guaranteed order unless you explicitly sort.

This means:

• First() returns whichever record appears first in the underlying dataset.
• Last() returns whichever record appears last.

These may change based on:

• Index changes
• Compact & Repair
• Query rewrites
• JET/ACE engine optimization

So the results are not stable or predictable.

As far as I can tell they are their only for backward compatibility and were meant to use on forms and reports not for writing sql.

 

[Isaac]

If you really need to know when a record was added, then you better add a column to the table with default Now()
This way whenever a record is inserted it stores the DateCreated (etc). Then query THAT. don't rely on any hacks (even max ID#) to do it

 

[ryetee]

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

I can but in talks with the user about this and he's questioning why he wants the due date anyway.

 

[Ken Sheridan]

If you really need to know when a record was added, then you better add a column to the table with default Now()
This way whenever a record is inserted it stores the DateCreated (etc). Then query THAT. don't rely on any hacks (even max ID#) to do it

I'm a strong supporter of having a DateTimeStamp column in a table, but assigning the return value of the Now() function to the DefaultValue property is not in itself sufficient. While it's fine if a row is being inserted by means of an INSERT INTO statement or by code into a recordset, in a bound form the value assigned to the DefaultValue property is the DateTime value when the record pointer is moved to an empty new record, which could be considerably before the user begins to insert any data.

The solution is to insert the value in the appropriate event procedure. Which one you use depends on whether you want the value to be when the user begins to insert data, or when the row is saved to the table. If the former then the form's BeforeInsert event procedure can be used. If the latter then the BeforeUpdate event procedure can be used, though in the latter case the assignment should be conditional on the form's NewRecord property being True. In either case the value should be assigned to the column's Value not its DefaultValue property.

 

[ryetee]

One thing that has been said here is that First() and Last() will return the first record added or the last record added.
That is definitely not true and not guaranteed. You might get lucky, but likely you may not. So you cannot count on these and therefore they should never be used for writing queries where you want the first added or last added record.

According to MS

Nowhere does it suggest this is the first or last record entered.

These are legacy JET functions and not part of ANSI SQL
Access tables have no guaranteed order unless you explicitly sort.

As far as I can tell they are their only for backward compatibility and were meant to use on forms and reports not for writing sql.

Not sure why it was used in the first place. I can't blame anyone else as I did the last amendment including "last"! I agree.
It does look like it's returning the row that appears last in the underlying dataset.

 

[ryetee]

If you really need to know when a record was added, then you better add a column to the table with default Now()
This way whenever a record is inserted it stores the DateCreated (etc). Then query THAT. don't rely on any hacks (even max ID#) to do it

Although I added the "last" bit in one of my amendments, the underlying design is not mine. If it was I'd have exactly that!

 

[ryetee]

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 when the order is paid in full it's dateless

 

[ryetee]

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.

In future I will but this has been resolved by not resolving it!! Going to remove the field from the query and report.

 

[ryetee]

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?

Yeah, never going to even think about using it again!