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

changing auto numbers

P: n/a
We have a database with 1600+ records in it.
They are linked across 7 tables by an ID type autonumber.

Now we want(not I want LOL) to remove the first 1200 records
no problem but they also want the ID to be reset to 1,2,3 ...

this would mean that record 1234=1 1235=2
With the id being linked across 7 tables with relationships...one to
one and some with one to many..can this be done without messing up the
links?

thanks for info I have never tried this before
Jerry
May 24 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
On Wed, 24 May 2006 13:01:13 GMT, sparks <js******@swbell.net> wrote:

This is a FAQ. Search at groups.google.com.
Short answer: this is a bad idea, and autonumbers should not have
meaning so "we" shouldn't care what their values are.

-Tom.
We have a database with 1600+ records in it.
They are linked across 7 tables by an ID type autonumber.

Now we want(not I want LOL) to remove the first 1200 records
no problem but they also want the ID to be reset to 1,2,3 ...

this would mean that record 1234=1 1235=2
With the id being linked across 7 tables with relationships...one to
one and some with one to many..can this be done without messing up the
links?

thanks for info I have never tried this before
Jerry


May 24 '06 #2

P: n/a
Thanks for backing me up.
The data analysis people came up with this.
BUT I run comparisons with those big numbers and my reports
don't look as nice as when I have 1 2 3 for the id so they flow.
(THAT IS WHAT HE SAID)
I suggested that he make an id number for each person and use that
instead..he got mad ROFL

again thanks for the help

Jerry

On Wed, 24 May 2006 06:48:47 -0700, Tom van Stiphout
<no*************@cox.net> wrote:
On Wed, 24 May 2006 13:01:13 GMT, sparks <js******@swbell.net> wrote:

This is a FAQ. Search at groups.google.com.
Short answer: this is a bad idea, and autonumbers should not have
meaning so "we" shouldn't care what their values are.

-Tom.
We have a database with 1600+ records in it.
They are linked across 7 tables by an ID type autonumber.

Now we want(not I want LOL) to remove the first 1200 records
no problem but they also want the ID to be reset to 1,2,3 ...

this would mean that record 1234=1 1235=2
With the id being linked across 7 tables with relationships...one to
one and some with one to many..can this be done without messing up the
links?

thanks for info I have never tried this before
Jerry


May 24 '06 #3

P: n/a
On Wed, 24 May 2006 17:49:47 GMT, sparks <js******@swbell.net> wrote:

Whenever you have an ID you show to the users, don't use Autonumbers.
Rather save the Autonumbers for the internal backbone, and add another
Long Integer field for the UserID that can be sequenced any way the
user pleases.
Shouldn't be too big of a task to implement that now.

-Tom.
Thanks for backing me up.
The data analysis people came up with this.
BUT I run comparisons with those big numbers and my reports
don't look as nice as when I have 1 2 3 for the id so they flow.
(THAT IS WHAT HE SAID)
I suggested that he make an id number for each person and use that
instead..he got mad ROFL

again thanks for the help

Jerry

On Wed, 24 May 2006 06:48:47 -0700, Tom van Stiphout
<no*************@cox.net> wrote:
On Wed, 24 May 2006 13:01:13 GMT, sparks <js******@swbell.net> wrote:

This is a FAQ. Search at groups.google.com.
Short answer: this is a bad idea, and autonumbers should not have
meaning so "we" shouldn't care what their values are.

-Tom.
We have a database with 1600+ records in it.
They are linked across 7 tables by an ID type autonumber.

Now we want(not I want LOL) to remove the first 1200 records
no problem but they also want the ID to be reset to 1,2,3 ...

this would mean that record 1234=1 1235=2
With the id being linked across 7 tables with relationships...one to
one and some with one to many..can this be done without messing up the
links?

thanks for info I have never tried this before
Jerry


May 25 '06 #4

P: n/a
I agree 100% with Tom.

Try to convince them it's just a meaningless number and it shouldn't
matter what the number is. It's not a good idea to make IDs visible in
an application if it can be avoided because this is precisely the thing
people want to do. If they dont want them changed they want to build
meaning into them. YUK!!

If you cant avoid doing it some things you might try:

I have writen queries before to populate tables with data where Access
has allowed me to specify the value going into an Autonumber field. So
you could try UPDATE tblA SET ID = (ID - 1233). Make sure you take a
backup before trying this as it is possible it will update 1233 records
to what you want and then when updating 2467 to 1234 think that there
is already a 1234 (because there used to be) and fail. Also make sure
all relationships where the ID is a foreign key are set to cascade
updates.

If that doesnt work -

Option 2.

This is pretty ugly because it gives you a good opportunity to stuff
the whole database up.

Create a new table identical to the existing one that you have to
change the ID for, except include an additional field called OldID.
eg TblOld tblNew
ID ID
fld1 fld1
fld2 fld2
OldID

Populate this table from the existing one:
SELECT fld1, fld2, ID as OldID into tblNew from tblOLD (check the
syntax for Select INTO query - I may have this wrong)

so now you will have a table with the IDs starting from 1.

For each table where ID is a foreign key you will have to drop the
relationship with tblOLD, then run a query to change the ID foreign key
to the new ID value. Lets call ID used as a foreign key in other
tables fID.

UPDATE tblNew inner join tblX on tblNew.OldID = TblX.fID SET tblX.fID =
tblNew.ID - Again I may have the syntax wrong. You have to have the
relationship dropped otherwise the update will fail because you are
changing the foreign key to a value that doesnt exit in tblOld.

I hope Access will allow this query to work because it could become a
bit recursive. Make sure you have backups - just in case.

I think you should be able to see what is trying to be achieved here.
If the query wont run then you should be able to write a program (form
and or function ) to do the same thing using a recordset ...

Then you have to redefine the relationship that used to exist between
tblX and tblOld with TblNew. It might even be best to change all
related tables first, rename tblOld to something else then rename
tblNew to tblOLd then re-establish the relationships.

If you point out the risk involved they might just change their minds.

Good luck (glad it's you)

Colin

May 27 '06 #5

P: n/a

This is a stupid idea please see Tom van Stiphout's post as to why.

You would do this on a copy of the db in case something goes wrong.

You would have to
delete the relationships to other tables
change the field type to number (long)
recreate the relationships ensuring you cascade updates
run the update
delete the relationships to other tables
change the field type to autonumber

And of course you would do this on a copy of the db in case something goes
wrong.

And of course this is a stupid idea please see Tom van Stiphout's post as to
why.

I like to repeat things when I think they need emphasising.

--

Terry Kreft

"sparks" <js******@swbell.net> wrote in message
news:p1********************************@4ax.com...
We have a database with 1600+ records in it.
They are linked across 7 tables by an ID type autonumber.

Now we want(not I want LOL) to remove the first 1200 records
no problem but they also want the ID to be reset to 1,2,3 ...

this would mean that record 1234=1 1235=2
With the id being linked across 7 tables with relationships...one to
one and some with one to many..can this be done without messing up the
links?

thanks for info I have never tried this before
Jerry

May 28 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.