My boss changed a field - help!!

J

jerseygirl

My boss was looking around my database, and it looks like he made some
changes. Every record gets an autonumber when I enter it. The numbers go up
sequentially, by one, for each record, and it keeps everything in order (the
field name is ID). Pretty standard. Well, after just entering 80 new
records, I've found a problem. The numbers now being assigned are long,
random numbers - positive and negative. How can I change them to be in line
with the other 4,974 numbers?
--
jerseygirl
~~~~~~~~~~
Sing like nobody''s listening
Dance like nobody''s watching
Love like it''s never gonna hurt
 
J

Jeff Boyce

Just an observation, but Access Autonumbers are intended to be used as
unique row identifiers. They are NOT guaranteed to be sequential and they
are generally unfit for human consumption.

It shouldn't matter whether they are "quasi-sequential" or "random".

That said, WHY does it matter in your situation?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

Dirk Goldgar

jerseygirl said:
My boss was looking around my database, and it looks like he made some
changes. Every record gets an autonumber when I enter it. The numbers go
up
sequentially, by one, for each record, and it keeps everything in order
(the
field name is ID). Pretty standard. Well, after just entering 80 new
records, I've found a problem. The numbers now being assigned are long,
random numbers - positive and negative. How can I change them to be in
line
with the other 4,974 numbers?
Click to expand...


While I agree with Jeff that it doesn't really matter whether an autonumber
is sequential or random, I do wonder what your boss did that changed yours
from sequential to random. Do you have more than one table with
autonumbers, and have the autonumbers gone random in all those tables? Your
boss may have just changed this one field, or he may possibly have
inadvertently replicated your database.
 
J

Jerry Whittle

You probably can blame the boss for this one. Autonumbers are guaranteed to
be unique with a table if they are also the primary key. Nothing more.

Access does have a setting to force the autonumbers to be really random.
Check the design of that field to see if that was changed. But even if set to
Increment, that doesn't meant that it won't skip numbers and other things.

However unless set to Random, I find the wide swing in numbers a worry. Make
a copy of the database then run a Compact and Repair to fix any problems.
 
J

jerseygirl

Dirk,

It looks like he did replicate my database (everything has that weird circle
with 2 arrows inside next to it), but there aren't two listed. The reason it
was important for them to be sequential was because that's how I keep all of
the records in order. I just entered a new one using my form, and when I
tried to go back to it from the next one, it wasn't there. It had been
assigned number -84593596 and was now at the beginning of all of my records.
This will make it difficult to keep track of things.
--
jerseygirl
~~~~~~~~~~
Sing like nobody''s listening
Dance like nobody''s watching
Love like it''s never gonna hurt
 
J

John W. Vinson

My boss was looking around my database, and it looks like he made some
changes. Every record gets an autonumber when I enter it. The numbers go up
sequentially, by one, for each record, and it keeps everything in order (the
field name is ID). Pretty standard. Well, after just entering 80 new
records, I've found a problem. The numbers now being assigned are long,
random numbers - positive and negative. How can I change them to be in line
with the other 4,974 numbers?
Click to expand...

You don't.

You've discovered one of many problems with using Autonumbers in a way that
assigns meaning to them. An Autonumber has one purpose, and one purpose only:
to assign a meaningless unique key to a record.

Your boss may have changed the field properties from Sequential to Random; or
may have "Replicated" the database (which would be a bit surprising if he's
not an Access expert).

You might need to create a new, empty database with the autonumber fields, and
then run Append queries to migrate all the data (all EXCEPT the autonumber)
into the new tables. Unless there is some information other than the
autonumber in the 80 new records to specify the desired sequential order,
you'll probably need to delete and reenter them.
 
J

jerseygirl

He replicated the database AND changed from sequential to random. If I
change back to sequential and delate all of my new records, will I be able to
get on with business as usual without having to create a new database?
--
jerseygirl
~~~~~~~~~~
Sing like nobody's listening
Dance like nobody's watching
Love like it's never gonna hurt
 
J

Jeff Boyce

If you've been keeping the records in order with Autonumber, you were doomed
from the start!

If you need to know which happened first, use a date/time field?

If you have a different, custom sort-order you need, add a field for that.

Don't expect Autonumber to be something it isn't.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
J

John W. Vinson

He replicated the database AND changed from sequential to random. If I
change back to sequential and delate all of my new records, will I be able to
get on with business as usual without having to create a new database?
Click to expand...

Replicating causes all autonumbers to become random all by itself (so that
users of different replicas can add new records independently with only a tiny
probability of getting duplicates).

It's hard to "unreplicate" a database. you would really be better off creating
a new one, importing the data, deleting the 80 "new" records with random
autonumbers, and reentering them. You should also add some OTHER field to your
table (such as a Date/Time field with a default value of =Now() ) to provide a
safer indicator of sequential order of records.
 
J

jerseygirl

What is replication?
--
jerseygirl
~~~~~~~~~~
Sing like nobody''s listening
Dance like nobody''s watching
Love like it''s never gonna hurt
 
J

jerseygirl

I created this database almost 3 years ago, and a previous one 3 years before
that. I never knew that I should not use autonumber to number my records.
I'm thinking that I'll just live with the way it is now for the next few
months, and start again at the beginning of next year.

Thanks to everyone for your help.
--
jerseygirl
~~~~~~~~~~
Sing like nobody''s listening
Dance like nobody''s watching
Love like it''s never gonna hurt
 
J

Jeff Boyce

!ALARM!

"... and start again at the beginning of next year."?!?

Are you saying that you create a new Access database for each year? If so,
you're committing spreadsheet on Access.

In a well-normalized relational database, you don't need to start over each
year, you just add a field/column to contain the date, and use queries to
get each year's data.

.... or am I reading too much into your last sentence?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

David W. Fenton

Replicating causes all autonumbers to become random all by itself
(so that users of different replicas can add new records
independently with only a tiny probability of getting duplicates).
Click to expand...

Random autonumbers are *required* for replicated databases -- they
couldn't possibly work without it.

I wonder why the boss replicated the database. Did anyone consider
whether or not it *needed* to be replicated? If it's not split, then
it's a REALLY BAD IDEA. If it's split, and only the data tables were
replicated, that's probably OK, assuming there's an actual need for
it.

Briefcase replication is *not* a viable solution, BTW, unless it's a
single user database.
It's hard to "unreplicate" a database.
Click to expand...

It's really not difficult at all. Question 10 of the Jet Replication
Wiki FAQ addresses this:

http://dfenton.com/DFA/Replication/index.php?title=FAQ

....and provides links to a number of utilities that make it pretty
easy to do.

you would really be better off creating
a new one, importing the data, deleting the 80 "new" records with
random autonumbers, and reentering them. You should also add some
OTHER field to your table (such as a Date/Time field with a
default value of =Now() ) to provide a safer indicator of
sequential order of records.
Click to expand...

You're assuming the boss will let him unreplicate.
 
J

John W. Vinson

It's really not difficult at all. Question 10 of the Jet Replication
Wiki FAQ addresses this:

http://dfenton.com/DFA/Replication/index.php?title=FAQ

...and provides links to a number of utilities that make it pretty
easy to do.
Click to expand...

Thanks, David. I knew that was out there somewhere but didn't remember where.

I'm guessing that "da Boss" did in fact create a briefcase replica, to take
home, not knowing what he was doing. But that's for the OP to explain if she
wishes to do so.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calendar colors 2
Record Order 3
Autonumber random becomes scientific 7
re-enter deleted record 2
Insert currrent date into selected record field with button ? 3
Autonumbering Old Records 2
Create a "history" for a field on a a table 1
Pull a random number more often 0

Top