Creative minds: how do we renumber with a query? (1 Viewer)

C

connie

Registered User.
Local time
Today, 08:08
Joined
Aug 6, 2009
Messages
92
Hi all!

I have a bunch of tables that have numbered data (but not Autonumbers). Oftentimes line items need to be removed. If I have to remove #50, for example, I then go back in there and re-number everything from 49 on manually. Is there an update query way to do this that I might tie to a command button that anyone can think of?

More extensively, there are subtables so if you clicked on Line #50 the subdata for that item would appear (linked by a hidden primary key). I would have to renumber all that as well. Needless to say I've been doing a lot of manual work!

If I had a starting point I think I could run with it...

Thanks!
 
V

Vassago

Former Staff Turned AWF Retiree
Local time
Today, 08:08
Joined
Dec 26, 2002
Messages
4,751
connie said:
Hi all!

I have a bunch of tables that have numbered data (but not Autonumbers). Oftentimes line items need to be removed. If I have to remove #50, for example, I then go back in there and re-number everything from 49 on manually. Is there an update query way to do this that I might tie to a command button that anyone can think of?

More extensively, there are subtables so if you clicked on Line #50 the subdata for that item would appear (linked by a hidden primary key). I would have to renumber all that as well. Needless to say I've been doing a lot of manual work!

If I had a starting point I think I could run with it...

Thanks!
Click to expand...

What exactly does this number link to? If you are renumbering, I can't imagine it's being used in queries to tie data together or your historical information would be hosed. I guess I'm just trying to understand the purpose of this field and even needing to go in and renumber. If it's for sorting, why renumber at all?

VBA can give you what you are looking for, but I'm just confused on what you are trying to do.
 
C

connie

Registered User.
Local time
Today, 08:08
Joined
Aug 6, 2009
Messages
92
Vassago said:
What exactly does this number link to? If you are renumbering, I can't imagine it's being used in queries to tie data together or your historical information would be hosed. I guess I'm just trying to understand the purpose of this field and even needing to go in and renumber. If it's for sorting, why renumber at all?

VBA can give you what you are looking for, but I'm just confused on what you are trying to do.
Click to expand...

Thanks for the response, Vassago :) Nope, it's not tying the data together - that is done by a hidden Autonumber primary key that's unchanging.

The answer is a long one, I'll try to make it short. There are numbered items that need to be inspected. If one fails, I'm told "#5 failed" and that's how I locate it. If one is mistakenly on the list or on the wrong list, I might get "#4 needs to be deleted," and then I end up with a list of 1,2,3,5,6,etc. I'm expected to just renumber the list when one is removed so that there are not number gaps in there and 5 becomes 4, 6 becomes 5, on and on.

Where the problem comes into play is that I get sent these lists back when items on there are tweaked (date change or something) from the local Excel versions employees keep (that don't have my hidden primary key or any unique fields, so numbering is all I can go by). So when I run my query to find unmatched items for making the updates, everything from #4 on would show up as unmatched because #4 has been deleted from my Access table but the returned Excel list numbers consecutively, so #4 is really the old #5, etc.

So in essence, this numbering is not used to link data in the Access version, but it IS used as the common denominator when I'm reimporting data.

The whole thing is an unfortunate situation but these list items have no unique fields themselves when I receive them so it was the best work-around I could use to tie anything together at all.
 
C

connie

Registered User.
Local time
Today, 08:08
Joined
Aug 6, 2009
Messages
92
Hi all...just wondering if this is not possible to do? I just Googled "renumber query access" to try and research more on my own and the first result was this post. :eek: Hopefully we can figure this out so if someone in the future wants to know how to do this there'll be answer on this post. In the meantime I'll keep looking on my own...
 
SOS

SOS

Registered Lunatic
Local time
Today, 05:08
Joined
Aug 27, 2008
Messages
3,517
You could use this function:

Code: 
Function RenumberTable(strTableName As String)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long
 
Dim strSQL As String
 
strSQL = "SELECT * FROM " & strTableName & " ORDER BY FieldWithNumberHere"
lngCount = 1
 
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL)
 
With rst
 
Do Until .EOF
   .Edit
   .Fields("FieldWithNumberHere").Value = lngCount
   .Update
   lngCount = lngCount + 1
Loop
 
rst.Close
Set rst = Nothing

(Air Code - Untested)

And you would call it like

RenumberTable "TableNameHere"

or

Call RenumberTable("TableNameHere")
 
C

connie

Registered User.
Local time
Today, 08:08
Joined
Aug 6, 2009
Messages
92
Awesome, thank you! Unfortunately I just finished renumbering a ton of them manually, but I should have more later today or Monday and I'll update this thread with how that goes.
 
You must log in or register to reply here.

Similar threads

A
Update the second table with the same item # and description using a query
2
Replies
22
Views
848
arnelgp
arnelgp
kstaelens
Transpose the result of a query and add it to another table
Replies
7
Views
271
kstaelens
kstaelens
M
How to query monthly updates field, past three months, where entry is "no new update"
Replies
9
Views
404
ebs17
E
V
A form using a query as a source doesn't refresh on changes in the table in some cases, and how to workaround this?
Replies
9
Views
342
Pat Hartman
P
G
Pasting data from Excel in Access Datasheet Form with Line Breaks
Replies
7
Views
184
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom