Finding the record with the maximum value in a query (2 Viewers)

[John O'Malvern]

I must be missing something simple, surely?
I have a membership database, with ContactID as autonumber, and I want my query to only give me the record of the most recently added member - which is obviously the highest number in ContactID. Right?
Max() and DMax() involve expressions referring to other fields or data [it seems to me] - a complication I can do without.
I did try what the Help suggested using 'top or bottom values' where I sort Descending, and select an option from the 'Return' box on the toolbar - but there's no option for one --------- Ohhhhh! I can just type in '1' -------- this works.
But my question remains:- why can I not just ask for 'the maximum' value in the criteria row of my query? (Sorry for the rant.)

 

[MajP]

you can use the Top X predicate

Select Top 1 * from sometable order by ContactID Desc

This returns the last record.

You could query on the max
Select * from someTable where ContactID = dmax("ContactID","SomeTable")

But you ask 2 different things.
You want the most recent record

want my query to only give me the record of the most recently added member - which is obviously the highest number in ContactID. Right?

Or do you want the highest value

why can I not just ask for 'the maximum' value in the criteria row of my query?

You can return the highest value, but that is different from the record with the highest value. Max and DMax return values not records.

 

[LarryE]

Research how to Group By in a query. Click Query Design and the Totals tab:

That will give you the option of Group By Max value in your ContactID field.

 

[Gasman]

I must be missing something?, what is wrong with DMax and no criteria?

 

[MajP]

I must be missing something?, what is wrong with DMax and no criteria?

As I pointed out it is unclear, but it appears that the OP wants the record with the greatest ID not the value of the greatest ID

want my query to only give me the record of the most recently added member - which is obviously the highest number in ContactID. Right?

 

[GPGeorge]

Are we also potentially running afoul of the mistaken assumption that "the most recently added member " will always be the one with the highest value in an AutoNumber field?
There are two aspects to understanding that question.

First, it is true that Access will, by design, assign the next value in sequence when adding new records. All else being equal, that should be the most recently added record.

On the other hand, it is not impossible that the way a user goes about selecting members to add can lead to members being added out of sequence, I.e. a member's paperwork from yesterday was overlooked, and added at the end of today's data entry, making that member "the most recently added". That member now has the highest value in the MemberID field using the AutoNumber by virtue of being added out of sequence, but that doesn't reflect the reality the member was added before anyone else today.

It's safer, in most cases, not to rely on AutoNumber sequences. Instead, I would use the Max() of a date field that accurately reflects the true sequence.

However, you go about determining who the "last" really is, you still have the question of whether you want the record of the ID value.

 

[arnelgp]

i asked Copilot and it suggested to use Last() sql function.

to confirm if CoPilot is right, i created a test table (Table1) with
following fields:

ID--------AutoNumber
Field1---Numeric (Long)

i open the table and add 3 new records (ids are 1, 2, 3)
created a query to use Last(ID), and it returns 3 (the last entry).

next using currentdb to insert record:

currentdb.execute "insert into table1 (id, field1) values (500, 1);"

the query returned 500 since that is the last record ID added.
again insert new record using:

currentdb.execute "insert into table1 (id, field1) values (20, 1);"

again the query returned the ID of the Last record added which is 20.

so i conclude that using Last() will give you "the record of the most recently added member"

 

[plog]

I want my query to only give me the record of the most recently added member
...
Why can I not just ask for 'the maximum' value in the criteria row of my query

Those are two different things. You absolutely can ask for the maximum value of a field:

SELECT MAX(YouriD) AS MaxID FROM Your table

Now if you want the rest of that record's data, you would use that query I posted as the basis of another query joined to Your table to get that record's data.

The reason you can't get it simply all at once is because it's two levels of data. You first need to look at all the data to get the highest ID, then you use that in a query at the individual record level to get a single record:s data.

 

[John O'Malvern]

Apologies for my confusion over the max value, and the record that has that.
I did indeed want the whole record.
And I remain surprised that there is not a simple criterium that selects only the highest value in a given field. (Due, no doubt, to my lack of understanding of how Access actually works. And please don't try to tell me, thanks!)
I take the point that the record with the highest Autonumber will not necessarily be the last record added, but in this case it will be ok.

 

[DHookom]

I would use:
Select * from SomeTable where ContactID = (Select Max(ContactID) From SomeTable)

 

[RonPaii]

Apologies for my confusion over the max value, and the record that has that.
I did indeed want the whole record.
And I remain surprised that there is not a simple criterium that selects only the highest value in a given field. (Due, no doubt, to my lack of understanding of how Access actually works. And please don't try to tell me, thanks!)
I take the point that the record with the highest Autonumber will not necessarily be the last record added, but in this case it will be ok.

MajP in post #2 gave you a solution, Use Top 1 sorting by ContactID.

 

[Ken Sheridan]

I agree with what George said in post #6, that relying on an autonumber column to reflect the sequence in which rows were inserted into the table is not completely bulletproof. For a reliable solution add a DateTimeStamp column of DateTime data type to the table. However, do not rely on the DefaultValue property being set to Now() to populate the column. Instead, assign the current date and time to the column in the BeforeInsert event procedure your data input form:

Me.DateTimeStamp = Now()

The reason for this is that the DefaultValue property of a control in a bound form assigns the current date and time to the control when the user moves to an empty new record in the form, which can be significantly different from when the user begins to insert data. In a multi-user environment this could result in the values not reflecting the order of insertion. The image below is from one of my demo files, and demonstrates this:

The DefaultValue property of the DateTimeStamp column should nevertheless be set to Now() to cater for those situations when a row is being inserted other than via a bound form.

 

[MajP]

George said in post #6, that relying on an autonumber column to reflect the sequence in which rows were inserted into the table is not completely bulletproof.

No that is not what George said. The autonumber will reflect the "sequence in which the rows were inserted", but the order in which they were inserted may not be the order in which they should have been entered. They could get entered out of order. So this is not a solution to his example. He is suggesting a manual date for when the person "joined" the organization.

AFAIK In access the autonumber is a realiable choice unless you set it to random, and if you did that you are going to know so then you would have to come up with another means. If the autonumber becomes unreliable then you have corruption in your database and that issue will far overshadow a query not being accurate. So I am curious of instances where the autonumber field is not a reliable source for when a record was added, outside of purposely choosing random or corruption.

 

[Ken Sheridan]

Perhaps I'm reading more into George's statement 'It's safer, in most cases, not to rely on AutoNumber sequences' than was intended, but I can recall a number of instances of posts in various forums over the last 30 years where the autonumber mechanism has inserted a value out of sequence. It's rare, and can justifiably be regarded as corruption, but Murphy's Law has always been one of my guiding principles. Autonumbers are fine where the values have no semantic significance, but not otherwise.

The OP did say 'to only give me the record of the most recently added member - which is obviously the highest number in ContactID' which to me clearly means inserted into the table. It might well be that's not what he actually meant, and George is right to identify the possibility of this, but it’s nevertheless conjecture, and would need to be confirmed by the OP before it can be concluded that time stamping is inappropriate.

 

[The_Doc_Man]

The issue of relying on "autonumber" fields for anything OTHER THAN a unique identifier for the record is ALWAYS questionable. To the best of my understanding, the autonumber is assigned when a new record is allocated, NOT when it is written back (committed). Which is why an autonumber (auto-increment) field can have gaps, such as after someone cancels a transaction that had allocated a record. You can't restore the number of the record you cancelled. It is always wrong to use an autonumber to have any other meaning than "unique record identifier" - though sometimes I could see using it after-the-fact as a transaction ID if you weren't expecting 4 billion transactions.

There is also an issue with using "First" or "Last" in any recordset that does not include a functional "ORDER BY" clause. Just because there IS a unique key field doesn't mean that the query you are using would automatically sort on that field. AND it has been observed MANY times that after a table has undergone selective updates, an unordered query will present records in chronological order of their most recent updates.

Pat Hartman and I argued over this one for a long time. Updating a record does NOT rewrite/overwrite the original record in place. It creates a new record that will eventually replace the old one. But if someone else had the DB open for, say, report generation, that original record will be used while the new one is being written - and that can't happen unless the new record is a modified copy of the old one, which implies they were written in different memory. And that old record's space, after it gets replaced, doesn't get recovered until you do a Compact & Repair operation. The impact of that is that "Last" is going to return the most recently updated record and "First" is going to return the least recently updated record, UNLESS there is an ORDER BY clause in effect.

 

[Ken Sheridan]

Updating a record does NOT rewrite/overwrite the original record in place. It creates a new record that will eventually replace the old one.

Date supports that in the context of the theoretical model. He points out that for a tuple to be updatable there would need to be such a thing as a tuplevar (tuple variable), which there is not. While he is using the terms of the database relational model, in this case I think we can legitimately apply the same reasoning to the physical model, in which a row in a table is, albeit somewhat loosely, equivalent to a tuple.

 

[GPGeorge]

I apologize. My inarticulate statement about "the last AutoNumber" poured fuel on a fire that has smoldered for years and occasional flares up.

What I wanted to express was the possibility that the most recently added record in a table may, or may not, represent the "last" record in a queue of transactions.

Take for example, a fictional scenario in which an organization accepts applications for membership. Prospects submit a form and payment for the memberships. Those forms go into someone's inbox where they sit until that person has time to enter the data into the database.

On any given day, up to 10 applications may be received. It should be obvious that the first one in the door at the crack of dawn is going to go into the inbox, where the second will land on top of it, the third on top of the second and so on.

If the data entry person picks up the application on top of the pile and enters it into the database, it will get an AutoNumber. The next one gets the next higher AutoNumber, and so on. They are, in fact, in the exact opposite sequence from the way they were received. In most cases, that shouldn't matter. Nothing critical depends on which was actually first or last.

However, it should also be obvious that the question, "Which application was the last one?" simply can't be answered independently of the rules for the workflow. If, by last, you mean, "the one the data entry person worked on at the end of the day," then, yes, the highest AutoNumber is highly likely to be that one.

But let's complicate the scenario. Let's say that the organization has only six slots per day, and therefore can't accept more than 6 applications on any given day. If the data entry person works the queue as before, the first four people who dropped off their applications are left out. That feels like punishment for being punctual. In that scenario, we simply can't make the decision about first and last based on the AutoNumbers because that represents the wrong meaning of "first" and "last" for this business rule.

We could make it even more complicated. We could say that the inbox gets turned over and all of the application end up in a pile on the floor. Now the meaning of "first" and "last" is totally arbitrary. Whichever one the data entry person happens to pick up gets to be first into the database.

All of this may seem beside the point, and depending on the actual intent of the OP's question, it may well be. Nonetheless, in the absence of an ordering principle independent of, and a priori to, the way records are entered into a table, relying on the highest AutoNumber as a fraught choice.

 

[John O'Malvern]

I agree with what George said in post #6, that relying on an autonumber column to reflect the sequence in which rows were inserted into the table is not completely bulletproof. For a reliable solution add a DateTimeStamp column of DateTime data type to the table. However, do not rely on the DefaultValue property being set to Now() to populate the column. Instead, assign the current date and time to the column in the BeforeInsert event procedure your data input form:

Me.DateTimeStamp = Now()

The reason for this is that the DefaultValue property of a control in a bound form assigns the current date and time to the control when the user moves to an empty new record in the form, which can be significantly different from when the user begins to insert data. In a multi-user environment this could result in the values not reflecting the order of insertion. The image below is from one of my demo files, and demonstrates this:

View attachment 123329

The DefaultValue property of the DateTimeStamp column should nevertheless be set to Now() to cater for those situations when a row is being inserted other than via a bound form.

Thanks, good points clearly made.

 

[John O'Malvern]

you can use the Top X predicate

Select Top 1 * from sometable order by ContactID Desc

This returns the last record.

You could query on the max
Select * from someTable where ContactID = dmax("ContactID","SomeTable")

But you ask 2 different things.
You want the most recent record

Or do you want the highest value

You can return the highest value, but that is different from the record with the highest value. Max and DMax return values not records.

Rather late, I thank you for this confirmation of what I found to work (in orig post). (Top 1 from ContactID Desc)

 

[John O'Malvern]

For the record ( ho, ho ) I am the only person adding new members to this database, and the peak rate at which they are added occasionally exceeds the giddy pace of 1 a month!
But I appreciate all the contributions to spell out the issues (which may become useful info elsewhere).
Thanks.