×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Using old IDs as Autonumbers

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.

RE: Using old IDs as Autonumbers

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

RE: Using old IDs as Autonumbers

(OP)
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.

RE: Using old IDs as Autonumbers

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

RE: Using old IDs as Autonumbers

Quote (TrekBiker)

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:

Insert Into MyNewTable(ID, Field1, Field2, ...)
                Select ID, FieldA, FieldB, ... From OriginalClientData 

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

RE: Using old IDs as Autonumbers

(OP)
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.

RE: Using old IDs as Autonumbers

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close