Restricting ID number length

[allaccess]

Hi,



Firstly, thanks for reading this! I'm very new to MS Access so I hope this question makes sense.

I work off a simple Access database that has two fields: an ID number generated by access, which I set to be four digits in length, and a field (Customer #) containing an eight-digit record number that is generated by an unrelated accounting system; they are not sequential numbers and are added every time I have a transaction to enter.

In the design view of the MS Access database, I have the format of the ID number listed as /1000. So, my current database looks something like this:

ID	Customer #
1000	98234561
1001	37770893
1002	45763945
1003	83284292

SO, I have entered enough Customer #s that I have now approached ID #1999. When I created a new record to enter a new Customer #, the ID number field added on a new digit to be a five-digit number, which is not what I wanted; I want it to proceed to 2000, 2001, etc.

This is what it looks like now:

ID	Customer #
1999	25255778
11000	65654656
11001	49476354
11002	24657837

Is there a simple fix for the number format to keep the ID field at four digits? THANKS IN ADVANCE!!!!

[Dal Jeanis]

Change it to /0000.

That 1 in the leading spot is a constant. The record you thought was 1002 is actually record number 2.

[allaccess]

Thanks!

I get that the "1" is a constant, but is there a way to keep my 4-digit IDs intact? Can I start the numbering of the database at 1000 so that it "re-numbers" the existing records and allows them to keep their existing IDs?

[Dal Jeanis]

Depends on how complicated your database is. Is that autonumber field used for ANYTHING else? Like, is it found on any other table?

If not, then if you want to take (for example) the record with the value 3 - that you have been displaying as 1003 - and change it to permanently have the value 1003, then do these steps:

1) Back up your database someplace safe, and do the rest of these steps in a test/junk version of the database.

2) Copy the entire table (data and structure) MyTable -> MyTableWork

3) Copy the entire table (structure only) MyTable -> MyTableNew

4) Change the autonumber field on MyTableWork to a number-type field (not key, not indexed, not autonumber).

5) Create an update query for myTableWork

UPDATE MyTableWork
SET ID = ID + 1000;

6) Run the update query.

7) Verify the results are what you wanted.

8) Create an append/Insert Query that looks something like

Insert Into MyTableNew (ID, [Customer #])
SELECT ID, [Customer #]
FROM MyTableWork;

9) Run that query.

10) See if the results are what you want.

11) If all of that worked, then you can take a fresh copy of your production database, delete the original table and import the corrected table from your junk database.

[June7]

Why is length of autonumber important? This field should be invisible to users. The Customer Number is the identifier they need to be concerned with.

[Dal Jeanis]

June - Since they're the only two fields in the table, it's intutitively obvious that assigning an internal number to the customer was the entire purpose of the table. Not a normal design, but it is what it is.

[June7]

Still don't understand the need for custom sequential ID field. Isn't Customer # unique? If ID is just an internal PK/FK link, doesn't matter if it is sequential or 1 or 10 digits.

BTW, advise no spaces or special characters/punctuation (underscore is exception) in field or object names. Better would be Customer_Num or CustomerNum

[Dal Jeanis]

Yeah, I hate # signs and spaces in field names.

I was assuming that a printout of the formatted autonumber IDs was being used for some business procedure. That's the reason why my whole update post 4 started with the explicit assumption that there's nothing more the database is using the ID field for. If there are any other tables using ID as a foreign key, then it will take three times that many steps to get it done.