Update field to Incremented Number (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
Hi, is it possible to use an update query to Number a field starting at 18618.
I already have a Select query which gets the required records and sorts them.
I've been doing this by moving through and editing each record, but wondering if an update query might do it instead.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:43
Joined
Oct 29, 2018
Messages
21,358
Hi. Are you talking about updating an Autonumber field or something else?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
Obviously you have a method for sorting, which is good - because if you didn't, you might as well assign random numbers to the records. Access tables have no immediately obvious order. Queries, because they can impose sorting via ORDER BY, could impose that order and make numbering work. But theDBguy's question is relevant. What are you trying to do here? WHY do want to do this?
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
No, it's a Number (Long integer field). There is an autonumber field but I've learned not to use this for any kind of sorting.
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
Its the field which my query sorts by. I want each record to be 1 more than it's predecessor. Then the total confirms no record is missed (or duplicated).
 

zeroaccess

Active member
Local time
Today, 18:43
Joined
Jan 30, 2020
Messages
671
Do you have a form with which these records are entered/edited? Or are you working just with tables?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:43
Joined
Oct 29, 2018
Messages
21,358
Its the field which my query sorts by. I want each record to be 1 more than it's predecessor. Then the total confirms no record is missed (or duplicated).
But if you're "updating" the table to assign these numbers, then how can you make sure you weren't missing any records? If you were missing records and you assign the numbers in sequence, then you wouldn't know there were missing records because the numbers are now in sequence (because you just updated them). Sorry, I'm a bit confused.
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
That's interesting.. you have me wondering now. My logic was the last (highest) number would equal the record count. But that would be true regardless. But I know they're wrong beforehand (which is why I want to update (fix) them.
(it is a query based on a Table).
 

zeroaccess

Active member
Local time
Today, 18:43
Joined
Jan 30, 2020
Messages
671

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
But I know they're wrong beforehand

How do you know this? The answer to this question might lead us to discuss your REAL goal.
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
The Rank Order query was worth seeing. I do have a rank order but it can't be set by a query as it's conditional on too many things (values in other fields in that record). Then it's possible that results in no rank, so a different assessment is done, and finally is still no rank established, it's an alpha sort within that group.
There's a class module that looks at each record and the one after it, and returns true if all these factors result in this record being less than the next record.
That's how I know they're right or wrong. If right the number field I was asking about updating should be sequential with no duplicates or skips. If it's not I update it. Although what DB guy said still worries me, am I missing something? But everything seems to work as intended.
An update query might be more efficient than moving through a recordset and reapplying the numbers as I've been doing.
Say I have an ordered Select Query, can I convert that to an Update and have it number a field from xx to xx+ its count? (xx will not always be 1.)

This field is also a helper if anything is moved, deleted or added.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
By having three different criteria you run into the issue of a problem complexity that a simple query might not manage. I do not know how you can do this short of some kind of function, but here is the next question. (I'm trying to wrap my head around this problem and so far it ain't wrapping so well.)

Is this strategy possible? First write a query that assigns your rank order based on those values in the other fields. IF you cannot, then don't assign anything. Write a second query that excludes everything that already has a rank order and then apply your "different assessment" to assert more rank orders. Finally, apply your alpha-sort to whatever is left without a rank order. Three queries with a divide-and-conquer approach.
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
Many thanks for your suggestion Doc_Man. It's not easy to explain, so I get it's pretty difficult to follow.
What you're saying means a major design change. That part (ranking) is working OK for many years so I'm not sure its warranted.
It may best to forget this Update Query idea and stick to what I have. No one's said it a good idea (or even do-able). It was a just a thought for improving one part of it, and may have had little impact. Cheers, thanks again for your time.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
It's not easy to explain, so I get it's pretty difficult to follow.

It is exactly this statement that tells me why you have a problem in the first place. The solution to a problem involves understanding the problem well enough to be able to fully document it. This is the heart of good solution design. If you can't explain it in print (even just for yourself) then there is some part of the problem that you have trouble in grasping. DON'T take that as a slam against you. It is just an observation that the really knotty problems need careful unraveling and that means you need to start with a more thorough analysis. I have an "Old Programmer's Rule" that applies here. The rule is: "If you can't do it on paper, you can't do it in Access." That is, if you can't sketch out the right algorithm on paper, how will you ever program the right algorithm in Access?
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:43
Joined
Jan 23, 2006
Messages
15,364
As has been said -If you can't describe it clearly, nobody can design/build it.
So try again in very simple English --what exactly are you trying to do. Forget database jargon for the moment.
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
The question was is it possible to use an update query to Number a field starting at 18618.
(18618 could be any number of course, that was just an example).
I don't need it now, but still interested what method might achieve it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
Yes, easily if the field is not Autonumbered. You can also do it if the field is autonumbered because you can diddle with the autonumber's next number in the FieldDef properties. However, the latter approach is highly NOT recommended.
 

kirkm

Registered User.
Local time
Tomorrow, 12:43
Joined
Oct 30, 2008
Messages
1,257
Autonumbers may not be sequential, as I understand it.
I'm curious where you'd apply the initial number, and if it could all be done within the query designer.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 18:43
Joined
Feb 28, 2001
Messages
27,001
Autonumbers are not sequential in cases where you start to create a record but retract it before saving. That record's number is committed and thus cannot be re-used for the next record even if you did a CANCEL later. Also, records can be deleted. Again, even if the last record is saved and then later deleted before another new record comes in, the number was committed.

As to doing it in the query designer, probably not practical to do it there. If you want a non-standard staring number, this is almost always a one-off operation which you would normally do by hand. After that, you would let the new number propagate via subsequent update queries.
 

Users who are viewing this thread

Top Bottom