Autonumber problem

[Guest]

I am dumping data from one Access Database to another. In the first DB I have
an autonumber field (INDEX) and the second has that field as a number field
Indexed with no duplicates. I also have an autonumber field in the second DB
with is indexed with no duplicates. The first DB dumps data to the second DB
on a weekly basis and when it dumps to the second DB the autonumber field
applies a format (2digit prefix number for the year)ie 045287 5287 record of
2004. This must be a unique number and preferably in numeric order.
Unfortunately, occassion it my bounce that number up to 10000 digits. I know
this is the best method of using this feature, but the second DB is for non
conforming product and is used by Quality COntrol and by our auitors. Is
there another solution more viable?

 

[John Spencer (MVP)]

If you need to do this, then you need to do this.

BUT, I would try to add a separate field to capture the year value. Just change
your index to be a multi-field unique index based on the year value and the
number value.

To create a multiple field unique index
--Open up the your table in design mode
--Select View: Index from the menu
--Enter A Name for Index in first blank cell under Index Name
--Select one field in the index under Field Name
--Set Unique to Yes
--Move down one line and select the next FieldName
--Continue moving down and selecting fieldnames until all needed are included.
--Close the index window and close and save the table

Now you can use the two fields and if you end up with 100,000 records or more,
you don't have to worry about it.

 

[Guest]

Actually I was using two indexes before but still using the format. I removed
the year format and had the same problem. You may be misunderstanding my
issue. It's not on the number of records but my Non Conf # index/autonumber
will got from say 045000 to 049300 or whatever when I run the append query
from the first database. All the unique number are sequential during the
dump, it's just that it starts the dump at a much higher number and goes on
from there.