Fixing Negative Autonumbers

[prophecym]

Hello

I have a big problem with my database. Some of our former IT guys messed our db up assigning random numbers for autonumber fileds. As you may guess, i have a lot of negative numbers assigned in fileds with the relationships in other tables.

I created a new field which assigned new autonumbers. I need to replace the old autonumber field (contains negative numbers) with the new field i created without messing the relations up.

For example, when Companies.CompanyID field assigned to new numbers, Contacts.CompanyID field needs to be replaced with the new numbers assigned to Companies.CompanyID field.



Thanks in advance.

[SteveF]

If your Companies table still contains both the old and the new CompanyID numbers, then a quick query will do the job.

It would be best to add a new column to the Contacts table to hold the new Company ID. Then, a query like this should do the update, using the "real" table and column names:

UPDATE Contacts
INNER JOIN Companies ON Contacts.OldCompanyID = Companies.OldCompanyID
SET NewCompanyID = Companies.NewContactID;

Delete the old Company ID columns and reset the relationships using the new columns & you're good to go.

Steve

[prophecym]

It gives me a syntax error when I try to save the query.
It says "Syntax error in UPDATE statement." But I couldn't figure the error out. Let me put my code here so you can see what I did.

UPDATE Registration
INNER JOIN Contacts ON Contacts.[ContactID1]=Registration.[Contact ID1];

I am hoping this will update the new Contact ID1 field in Registration table.

This is going to be probably a dumb question. The new ID field I created needs to be autonumber field, and when I choose that field to be that way, it automatically assigns the numbers. So I don't know this query would work on autonumber fields which has already filled up with numbers.

[SteveF]

It gives me a syntax error when I try to save the query.
It says "Syntax error in UPDATE statement." But I couldn't figure the error out. Let me put my code here so you can see what I did.

UPDATE Registration
INNER JOIN Contacts ON Contacts.[ContactID1]=Registration.[Contact ID1];

I am hoping this will update the new Contact ID1 field in Registration table.

This is going to be probably a dumb question. The new ID field I created needs to be autonumber field, and when I choose that filed to be that way, it automatically assigns the numbers. So I don't know this query would work on autonumber fileds which has already filled up with numbers.

The syntax you've got is definitely a problem. Here's a "generic" SQL with the values it's looking for:

UPDATE <table name to be updated>
INNER JOIN <table to update from> ON <table name to be updated>.<old common field> = <table to update from>.<old common field>
SET <table name to be updated>.<new field> = <table to update from>.<new field>;

You need all three sections: UPDATE, INNER JOIN, and SET.

In your Contacts table, the field which contains the Companies ID number SHOULD NOT be an autonumber. Set it to Number - Long Integer, so that it has the same number type as the Autonumber in Companies.

Steve

[prophecym]

Thank you very much Steve,
It works. I missed the SET parameter at the first one. You saved me from wasting a lot of time.

[SteveF]

Glad to hear it worked!

Please mark the thread as solved so that others in the forum can pay attention to the unsolved items.

Steve