Using old IDs as Autonumbers
Using old IDs as Autonumbers
(OP)
In an old database a client has 1500 customer records with IDs that are just numbers. The same table has products assigned to the customer, awful practice.
In creating a new database that outs client and product data into separate tables is there a way of adding a new CustomerID Autonumber field, but retaining the old IDs? There is so much other history using the old IDs that it has to be retained, so insisting on switching to new numbers isn't an option.
Talk To Other Members
RE: Using old IDs as Autonumbers
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Using old IDs as Autonumbers
Tried this and no success. Appending the client data including the original ID s gave error 'You tried to assign the Null value to a variable that is not a Variant data type'. Doing the same without the old ID just resulted in a set for sequential new IDs.
I'm going to try creating a new table with IDs starting at 1 and extending to 1500 records, and all other fields left blank. Then appending these field from the original Client table.
RE: Using old IDs as Autonumbers
I hope you are fully recovered from your bike mishap. I have moved to indoor cycling here because of the snow and ice (and my age).
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Using old IDs as Autonumbers
Which is it? Your ID's are or are not numbers?
If your original client data have unique ID's that are numbers, and your new table has an ID column that is an AutoNumber, you should be able to populate your new table with your original 'client data' like:
Insert Into MyNewTable(ID, Field1, Field2, ...) Select ID, FieldA, FieldB, ... From OriginalClientDataBecause "You can (insert data into an Autonumber field.) if you use an append query and the value doesn't duplicate an existing autonumber." - mentioned here
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson
RE: Using old IDs as Autonumbers
The IDs were all numbers but following Duane's suspicion I found errors when I looked closely. The crunch was having two with the same number, bad effect of not having an Autonumber from the start.
My first attempt with a new ID and some rearrangement gave Autonumbers but slightly out of sync.
Instead I created a blank copy of ClientData with a new Site Code Autonumber. Then created 1500 blank records, including an extra 'Old Site Code' field with matching numbers. Then populated all remaining fields in a query with ClientData, linking the old IDs. All fine.
Andy's proposal looked as though it would also work, so thanks for that.
PS Duane, the bike accident was to my wife, who hit a bad bit of road that threw her off, breaking her kneecap and bringing up a huge swelling around her eye. It's improving but the leg brace has to stay on for 6 weeks.
RE: Using old IDs as Autonumbers
It should, assuming the
Select ID, FieldA, FieldB, ... From OriginalClientData
would return records with unique ID's (as Numbers)
> having two (records) with the same number
Easy to detect, run:
SELECT ID, COUNT(ID) AS NumOccurrences
FROM OriginalClientData
GROUP BY ID
HAVING ( COUNT(ID) > 1 )
ORDER BY COUNT(ID) DESC
The outcome will show you which ID's appear more than once.
---- Andy
"Hmm...they have the internet on computers now"--Homer Simpson