Need a quick way to change ID fields - Update Query?

[elinorigb]

I need a quick way to change the corresponding data in Table2 to new values that have come into existence for my Table1. With all that these tables have been through (see below if you want the whole story), the data value for my primary keys in each table was simply set for "number" instead of "autonumber" since there were already values existing during data transfers (exports and imports to and from xls spreadsheets). Of course for my primary key in Table 1, I am not able to change the data type from "number" to "autonumber". This means I need to add a new autonumber field to my first table and make it the primary key instead. This causes problems with the relationship between Table1 and Table2, as Table 2 has hundreds of records, and is related to Table1 by Table1.ID, which I now need to change. I need a quick way to change the corresponding data in Table2.IDforTable1 to the new values for Table1.ID. I thought about using "Find & Replace", but this is time consuming and leaves room for error. Perhaps an update query would work? I have not worked much with this type of query. If anyone has other suggestions, I would greatly appreciate the help. For more background info on how I got to this point, you may read the next portion, but this is not necessary. Thanks again.

This problem stems from having to cleanup someone else's Access "database", which was really just one huge table with a ton of duplicated information. Since the table had thousands of records, I decided to use a table analyzer, and then split the table in the best way I could. The table split uses the "lookup" fields, rather than a number. So I exported the three tables as excel spreadsheets to do some other data cleanup. Exporting the tables to Excel automatically changed the lookup fields to their corresponding ID number. When I imported the Excel spreadsheets back into access, everything worked well, relationships, etc., only I need the primary keys for each table to be Autonumbers, so that the person who uses the database won't have to enter in a primary key value.

 

[randy700]

How about...

UPDATE Table2
SET [COLOR=blue]YourFieldName[/color] = Table1.[COLOR=blue]TheAutonumberFieldName[/color]
WHERE Table1.[COLOR=blue]YourFieldName[/color] = Table2.[COLOR=blue]OriginalFieldName[/color]

Note: Not tested.
You may have to remove relationships first.
Make a backup before running the query.


Randy

 

[elinorigb]

Thank you for your suggestion. It made sense to me and I was able to set up the query as an update query just fine with no syntax errors. However, I keep getting the following error message:

Query1 is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long. (Error 3125)

I am naming the query something very generic. I think the data has just been through too much. I tried this in a couple different versions of the database. With all the trouble i've had, I have several versions of it. But thanks anyway for your helpful suggestion. It looks like its "Find and replace" for me.

 

[lespaul]

In your OP you said two tables, and now you say there's an error about a query. If you are working from a query there are specific rules about updating information. Why don't you share the SQL you are running and getting an error from?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[elinorigb]

Happy to share it:

UPDATE [Table2] SET [Table2].Table1OldID = Table1.NewID
WHERE ((([Table1.OldID=Table2.Table1OldID])<>False));

Let me know if you have question. Again, I get no syntax errors, just the error when I try to run the query. Thanks!

 

[PHV]

UPDATE Table2 INNER JOIN Table1 ON Table2.Table1OldID = Table1.OldID
SET Table2.Table1OldID = Table1.NewID

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[elinorigb]

Thanks for the suggestion. Once I figure out the Access bracketing, maybe I can test it. Right now I can't get past the syntax errors. A couple of years ago I was used to the bracketing SQL in Access for SELECT statements, but I'm rusty now and have never worked much with UPDATE queries in Access. I do appreciate everyone's help. Am almost finished with the problem using the old "find and replace" method...Only a day's work, roughly. I am open to other comments to learn from my experience, but its my own fault for not remembering the bracketing rules better. Thanks again.