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

[DHookom]

As this thread clearly exposes, seasoned developers find “… but in this case it will be ok” incomprehensible

 

[John O'Malvern]

As this thread clearly exposes, seasoned developers find “… but in this case it will be ok” incomprehensible

I'm sure that's based in good professional sense. I, OTOH, have a very controlled situation with limited consequences.

 

[arnelgp]

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

it is the same as using Dmax(ContactID).
again it will give you the highest autonumber (if contactID is autonumber) but does not
guarantee that it is the "most recently added member".
since there is known issue with Autonumber being "jumpy".
meaning, you can manually insert an autonumber (using sql) that does not exists and
that will be the start of the new seed.

take for example contactID's 1, 2, 3
supposed that the "most recently added ContactID = 3.

you delete record with ContactID 2, still the most recent is 3.

now, you insert a record using SQL, bringing back ContactID=2 (because you are thinking to prevent the gap).

insert into YourTable (ContactID, FirstName) Values (2, "SampleName")

using either DMax() or "Select Top 1 ContactID from YourTable Order By ContactID Desc;"
will bring ContactID = 3 as your result (and you think this is correct?)

now, try the Last() and it will return 2 (which is correct, the most recent record).

 

[John O'Malvern]

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

it is the same as using Dmax(ContactID).

It thought it had been established (very early on) that DMax will return the value of ContactID? (i.e. a number)
What I was saying was that Top1... gave me the record, which is what I needed.
I understand that autonumber *may* in some circs not assign the highest number to the last record.

 

[Ken Sheridan]

now, you insert a record using SQL, bringing back ContactID=2 (because you are thinking to prevent the gap).

As you've touched upon the subject of filling gaps in a sequence of primary key values, I've never seen the point of this, but some people do seem to have a horror vacui. For them the following query is from one of my demo files :

SELECT
    ProductIDs.ProductID
FROM
    ProductIDs
    LEFT JOIN Product ON ProductIDs.ProductID = Product.ProductID
WHERE
    ProductIDs.ProductID <= (
        SELECT
            MAX(ProductID)
        FROM
            Product
    ) + 1
    AND Product.ProductID IS NULL;

The ProductIDs table is just a list of integer numbers from 1 to some high value and is easily generated with a little code. The key of the Product table is not an autonumber. The DefaultValue for the key is obtained in the form's Current event procedure with:

If Me.NewRecord Then
     Me.ProductID.DefaultValue = """" & GetProductID & """"
End If

The GetProductID function is:

Private Function GetProductID()

    GetProductID = Nz(DMin("ProductID", "qryProductIds"), 1)
    
End Function

To cater for conflicts in a multi-user environment the form's Error event procedure contains the following line of code:

Response = IncrementField(DataErr)

The IncrementField function is adapted from Roger Carlson's well known method for generating sequential key values:

Private Function IncrementField(DataErr)

    Const KEYVIOLATION = 3022
    
    If DataErr = KEYVIOLATION Then
        Me!ProductID = Nz(DMin("ProductID", "qryProductIDs"), 1)
        IncrementField = acDataErrContinue
    End If

End Function

 

[arnelgp]

What I was saying was that Top1... gave me the record, which is what I needed.

be in peace with your solution then.
you dont get what we are saying about autonumber field.

 

[DHookom]

be in peace with your solution then.
you dont get what we are saying about autonumber field.

I think he “gets it” but is at a level of OCD (Obsessively Controlled Developing) below the bar set by many here.

 

[The_Doc_Man]

As you've touched upon the subject of filling gaps in a sequence of primary key values, I've never seen the point of this

Ken, in some systems a contiguous numbering scheme is used to show that no transactions have been missed. Happened on my watch with the U.S. Navy where my personnel management system had to exchange sequentially numbered transaction records with 18 other agencies. Of course, each agency had their own separately tracked number for each potential partner agency. If we had a numbering gap, it turned into a full-fledged auditing event that involved inquisitors investigators from Bureau of Naval Personnel, plus other officials. In Access, the "normal" autonumber field would not be acceptable and we would have to generate the next contiguous number ourselves to assure we left no gap. It's sort of analogous to documents that say "this page intentionally left blank" when in fact we KNOW that the only think blank is the auditor's mind.

Some financial systems also have that sequentially-numbered-transaction rule. While most accountants are quite decent folks, some of them can be SO anal-retentive about non-contiguous number sequences.

And THAT is why sometimes those gaps matter.

 

[John O'Malvern]

I think he “gets it” but is at a level of OCD (Obsessively Controlled Developing) below the bar set by many here.

I do indeed. (And yes, thank God! )
'Chill', guys!

 

[RonPaii]

Ken, in some systems a contiguous numbering scheme is used to show that no transactions have been missed. Happened on my watch with the U.S. Navy where my personnel management system had to exchange sequentially numbered transaction records with 18 other agencies. Of course, each agency had their own separately tracked number for each potential partner agency. If we had a numbering gap, it turned into a full-fledged auditing event that involved inquisitors investigators from Bureau of Naval Personnel, plus other officials. In Access, the "normal" autonumber field would not be acceptable and we would have to generate the next contiguous number ourselves to assure we left no gap. It's sort of analogous to documents that say "this page intentionally left blank" when in fact we KNOW that the only think blank is the auditor's mind.

Some financial systems also have that sequentially-numbered-transaction rule. While most accountants are quite decent folks, some of them can be SO anal-retentive about non-contiguous number sequences.

And THAT is why sometimes those gaps matter.

You see the same gap problem in invoicing. To solve it the invoice number is generated and applied only after the invoice record is inserted. The record can only be voided, never deleted.

 

[Ken Sheridan]

Ken, in some systems a contiguous numbering scheme is used to show that no transactions have been missed.

I understand that. My comment related not to maintaining an unbroken sequence, but to filling gaps in a sequence caused by the deletion of prior rows, or aborting the entry of a new row where the key is an autonumber. The ability to replace a 'missing' row with with another with the same key, which I've seen requested from time to time over the years, is what I can see no cogent justification for, beyond a desire for superficial neatness, though malice intent cannot be ruled out in some contexts.

 

[The_Doc_Man]

I see. After the fact, plugging holes in a number sequence (where contiguity and order of appearance are significant) is of questionable honesty and/or accuracy. There, I agree with you.