By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,060 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Update Query to Renumber a Column with a Starting Number

P: 12
I'm trying to find a simple way to update a column with sequential numbers without using a recordset. Here is the catch, each agency has a range such as Anaheim - 6,000 range Buena Park - 7,000 range... most of the records have already been numbered but there is a set that is not and the renumbering needs to start where the other set left off. So the Anaheim would start at 6,292. I have 29 different agencies, each with a different starting number (which I have in a separate table). Can anyone help out??

Oct 27 '08 #1
Share this Question
Share on Google+
17 Replies

Expert Mod 15k+
P: 31,487

Can you explain, possibly using some example data, what it is you're actually trying to achieve?
Oct 27 '08 #2

P: 12
For reasons unknown to me the data looks like this right now (although there are many more than this many records for each agency... hence the need for some automation)

BREA 9119
BREA 9120
BREA 9122

I need it to be more like this:

BREA 5445
BREA 5446
BREA 5447
Oct 27 '08 #3

Expert Mod 15k+
P: 31,487
That helps to a certain extent.

Are you wanting to update the existing records such that they match the data, but in place? In the same table? If so, it would probably make more sense to prepare the data into a new (if duplicate) table first, then move the data back (and delete the originals) when that has been successfully accomplished.
Oct 27 '08 #4

P: 12
Yes I would like to update the records in place, in the same table - the table I'm working with is already a copy of the original, so there is no worry about messing anything up. But either would work for this situation.
Oct 27 '08 #5

Expert Mod 15k+
P: 31,487
Right, if I post the concept first, I will follow it with some SQL if you say you need it.

It's better (obviously) if you can sort it out yourself from the concept, but it is a little more complex than most so I will do some for you if required.

To enable communication I will start by giving some names to the tables. It's hard to talk about (and be understood) if we're dealing with three separate tables without names.
Expand|Select|Wrap|Line Numbers
  1. tblIn     - Current data
  2. tblOut    - New data (Same layout as tblIn)
  3. tblAgency - Starting numbers for each Agency
We need to add records into [tblOut], from [tblIn], where the numeric value ([Num]) is the matching value from [tblAgency] if it is the first record, or if it is not, then it should be the next one in the sequence where [Agency] matches tblIn.Agency.

Normally, at this stage we would be thinking of linking in tblOut into the input stream to check it on an ongoing basis. Unfortunately, this seems to access the data BEFORE any are added in. This means that the updates are not visible to the process while it's running - thereby sabotaging the logic completely :(

To illustrate - the SQL would be something like :
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblOut (Agency,Num)
  3. SELECT tI.Agency,
  4.        Max(IIf(tO.Agency Is Null,tA.Num,tO.Num+1)) AS Num
  7.       tblAgency AS tA
  8.   ON  tI.Agency=tA.Agency) LEFT JOIN
  9.       tblOut AS tO
  10.   ON  tI.Agency=tO.Agency
  12. GROUP BY tI.Agency,
  13.          tI.Num
I know I wasn't going to post the SQL yet, but as it turns out this won't work in simple SQL anyway. I also tried using DMax() but that (strangely) suffered from the same problem.
Oct 28 '08 #6

Expert Mod 15k+
P: 31,487
That means, of course, that VBA coding with RecordSets will be required to accomplish this. The logic is the same, but the code will be different.

Let us know if you need further pointers on this (specific questions would be best).
Oct 28 '08 #7

P: 12
So would I start out with the same data in both TblIn and TblOut? Then would I run SQL commands only or do some kind of loop?
Oct 29 '08 #8

Expert Mod 15k+
P: 31,487
You already have data in [tblIn].

On a record by record basis you need to add those records to [tblOut] using VBA Recordset processing. You should end up with all the same data in [tblOut] as you had in [tblIn] except the numbers should be in the ranges you require.

When you're happy with the data, simply clear down [tblIn] and copy the data back across. [tblOut] can then be deleted as it will have served its purpose.
Oct 30 '08 #9

P: 12
Got it. I am having difficulty with the joins though and this part:

Max(IIf(tO.Agency Is Null,tA.Num,tO.Num+1)

Wouldn't the tO.Agency always be null because starting out there is nothing in tO?
Oct 30 '08 #10

Expert Mod 15k+
P: 31,487
I'm afraid that code is an example of what doesn't work. This, and the explanation why, is included in the post.

Let me know if you need more help with this.
Oct 30 '08 #11

P: 12
This is what I've come up with so far, as pseudocode for what I want to do, I think I can figure it out except for what I want to do in step 2, is there a feasible way to do step 2?


Table_IN – All of the records that need updated, all Nodes with ID_OCTA >=9101 (191 records)

Table_OUT – All of the same fields as Table_IN but empty, contains no records

MaxOfID_OCTA – Created from Nodes Table, grouped by agency and Max of ID_OCTA, this provides the starting number for each agency


1. Inner Join between Table_IN and MaxOfID_OCTA on AGENCY field where joined fields from both tables are equal, this query will be called INNERJOIN_Table_IN_Agency

ID_OCTA field comes from the MaxOfID_OCTA query/table so that the ID_OCTA field is the same for each agency entry, for example, the ID_OCTA field is 9293 for every “Anaheim” entry

2. Create query that will display all 192 Records from Table_IN but only one entry for ID_OCTA from the table INNERJOIN_Table_IN_Agency, we will call the new table resulting from this query “RecordsetData”

The table should read similar to this:

Anaheim 9293
Anaheim Null
Anaheim Null
Anaheim Null
Brea 9177
Brea Null
Brea Null
Brea Null
Buena Park 9214
Buena Park Null
Buena Park Null

3. Establish a DAO recordset from “RecordsetData”

4. Run SQL action query which will INSERT INTO Table_OUT WHERE RecordsetData.ID_OCTA is NOT null
Else If RecordsetData.OCTA_ID IS null, then INSERT * INTO Table_OUT WHERE ID_OCTA is NOT NULL + 1, GROUP BY “AGENCY”

Loop until End of Recordset

5. Table_OUT will have the results
Oct 30 '08 #12

Expert Mod 15k+
P: 31,487
I'm afraid SQL doesn't work that way. And also your problem seems to have changed a bit (along with names of items). Your first post refers to a table which stores the starting point of what was [ID_Num] (but now seems to be [ID_OCTA]) for each Agency. Now you have a table [MaxOfID_OCTA] which doesn't sound the same at all.

You will need to use DAO.Recordset VBA programming to change the number for each record. If I am to help at all I will need some clearer information as to what you're working with. [ID_Num] or [ID_OCTA] is fine, but I need to understand what is what.

This will work much better if you can post the meta-data (info about the layout / structure) of the tables in the same way as I use in my example. Click on the Reply button and you will have access to all the codes I've used. PK & FK stand for Primary Key & Foreign Key respectively. Never use TABs in this as the layout gets mucked up. Use spaces and all is fine.
Table Name=[tblStudent]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. StudentID       AutoNumber    PK
  3. Family          String        FK
  4. Name            String
  5. University      String        FK
  6. Mark            Numeric
  7. LastAttendance  Date/Time
Oct 30 '08 #13

Expert Mod 15k+
P: 31,487
I should add that you have obviously tried to get to grips with this yourself. I respect that. It makes spending time trying to help more worthwhile.

There's still a little way to go yet, but I'm sure we can get this resolved.
Oct 30 '08 #14

P: 12
Let me do some better explaining. Basically I have one table that I want to update, the "Nodes" table. This has about 7,000 traffic signal nodes which each have a responsible "Agency" and an "OCTA_ID" number. All of the OCTA_ID's < 9101 are correct and do not need to be changed. All of the OCTA_ID's >= 9101 are not correct and need to be changed - these amount to about 190 records. (I know it would not take that long to just go in and manually change them but that is not my assignment). So, the MaxOfID_OCTA comes in because the renumbering has to start where it left off with the OCTA_ID's < 9101 (The correct records). I need some way to update the Nodes table with the new OCTA_ID's... they don't have to be in any particular order, I just have to make sure to only update the ones with OCTA_ID >=9101.

Table Name=[Nodes]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. ObjectID     Autonumber     PK
  3. Shape        OLE Object
  4. SIGNAL      Number
  5. AGENCY    Text     Agency which "owns" the node 
  6. TIER           Number
  7. PROJECT_IN     Text
  8. CONTROL     Text
  10. SYSTEM     Text
  11. NOTES_COMM     Text
  12. CYCLE_LENG     Text
  13. NAME     Text
  14. FORUM_NO Number
  15. PED_SIGNAL    Text
  16. ST_NAME     Text
  17. Count     Number
  18. ID_OCTA     Number   Anything >=9101 Needs Changed 
  19. POINT_X     Number
  20. POINT_Y     Number
Table Name=[MaxOfID_OCTA]
Expand|Select|Wrap|Line Numbers
  1. Field           Type      IndexInfo
  2. AGENCY     Total: Group by
  3. ID_OCTA     Total: Max 
I need to keep all the fields from nodes, but the only ones relevant to this problem are "AGENCY" and "ID_OCTA" (I've been using "ID_OCTA" interchangeably with "OCTA_ID" - sorry for any confusion). Ultimately this table will be used in ArcSDE (reason for all of the other fields).
Nov 3 '08 #15

Expert Mod 15k+
P: 31,487
I'm sorry I've been a little tied up during the last week and haven't had time to look at this problem. I hope to find time shortly to give this some more attention. Thank you for your patience.
Nov 11 '08 #16

P: 12
No worries, talk to you soon.
Nov 11 '08 #17

Expert Mod 15k+
P: 31,487
Forget any SQL queries. SQL has the disadvantage (in this case) of being optimised and working directly from the data available at the start. This means that updates made during the process are NOT immediately reflected in the input. Hence the failure of all previous attempts.

You need some code (as I mentioned before in post #35) which moves the data from Table_IN to Table_OUT (forget MaxOfID_OCTA - that will be of no help to you in this process - too static).

The logic would be of the following form :

1) Read in a record from Table_IN.

2) Copy data from the Table_IN fields to the Table_OUT fields.

3) If ID_OCTA > 9101 use the following line to set up the new ID_OCTA :
Expand|Select|Wrap|Line Numbers
  1. rsTO.ID_OCTA = DMax("[ID_OCTA]", _
  2.                     "[Table_OUT]", _
  3.                     "[AGENCY='" & rsTI.AGENCY & "'") + 1
4) Save Table_OUT record (Use rsTO.New & .Update to add the record and commit it).
Nov 12 '08 #18

Post your reply

Sign in to post your reply or Sign up for a free account.