Using old IDs as Autonumbers 1

[TrekBiker]

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.

 

[dhookom]

I would try create a new table with the same structure and change the ID to autonumber. Then append from your existing table into the new table.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks for responding, as ever, my acknowledgement delayed by a bike accident.

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.

 

[dhookom]

Does every customer have a unique ID that is numeric? It sounds like you are attempting to insert a null value into a field that doesn’t accept nulls.

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

 

[Andrzejek]

1500 customer records with IDs that are just numbers
ID s gave error 'You tried to assign the Null value...

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:

[pre]
Insert Into MyNewTable(ID, Field1, Field2, ...)
Select ID, FieldA, FieldB, ... From OriginalClientData[/pre]

Because "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

 

[TrekBiker]

Thanks both

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.

 

[Andrzejek]

>Andy's proposal looked as though it would also work

It should, assuming the
[tt][blue]Select ID, FieldA, FieldB, ... From OriginalClientData[/blue][/tt]
would return records with unique ID's (as Numbers)

> having two (records) with the same number
Easy to detect, run:[tt][blue]
SELECT ID, COUNT(ID) AS NumOccurrences
FROM OriginalClientData
GROUP BY ID
HAVING ( COUNT(ID) > 1 )
ORDER BY COUNT(ID) DESC[/blue][/tt]
The outcome will show you which ID's appear more than once.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson