Results 1 to 6 of 6
  1. #1
    prophecym is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    5

    Exclamation Fixing Negative Autonumbers

    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.
    Last edited by prophecym; 02-03-2011 at 03:53 PM.

  2. #2
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    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

  3. #3
    prophecym is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    5
    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.
    Last edited by prophecym; 02-03-2011 at 11:37 AM.

  4. #4
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    Quote Originally Posted by prophecym View Post
    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

  5. #5
    prophecym is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2011
    Posts
    5
    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.

  6. #6
    SteveF is offline Generally AccessAble
    Windows XP Access 2010 32bit
    Join Date
    Nov 2010
    Location
    Fourth Corner
    Posts
    123
    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

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help Needed Fixing User ID
    By aamer in forum Access
    Replies: 1
    Last Post: 10-29-2010, 07:27 PM
  2. Fixing Table Design
    By bolivartech in forum Database Design
    Replies: 7
    Last Post: 09-29-2010, 07:12 AM
  3. re-autonumbers every year by access
    By migo702000 in forum Access
    Replies: 4
    Last Post: 09-20-2010, 11:31 AM
  4. need a little help fixing an SQL error...
    By markjkubicki in forum Queries
    Replies: 3
    Last Post: 08-04-2010, 06:15 AM
  5. AutoNumbers
    By remmons in forum Access
    Replies: 1
    Last Post: 01-30-2010, 01:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums