How to change data in a field based on a field in another table? 1

[TSimonick]

Hi,

I have an Orders table with a field that contains product numbers. The product numbers are the real product numbers, not the Product_ID (autonumber) from the Products table. This was a mistake because I need to change product numbers in the Products table and they won't be updated in the Orders table.

Can anyone show me how to write some code that would change the product numbers in the Orders table to the Product_ID in the Products table? I only need to do this once, but I have 1000's of records. :-(

Any help would be greatly appreciated!!

Tom

 

[SBendBuckeye]

Do you have a relationship defined between the product numbers in the 2 tables, or a relationship defined between the keys of the 2 tables? If not, do you have any other kind of logical relationship you could potentially use.

It would be helpful if you could post some examples of your current data, keys, product numbers, etc.

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

Thanks for your time!

Currently:

Product Table:
Product_ID (autonumber)
ProductNumber (long) Primary Key
ProductName (text)

Order Table:
Order_ID (autonumber) Primary Key
Product (long)
etc.

Join: Product Table.ProductNumber to Order Table.Product

What I should have done was join on Product Table.Product_ID, so that if I changed ProductNumber they would have just been referenced in the Order table.

So, what I need to do is look at each record in the Order Table.Product field and substitute the Product_ID.

Is that possible?

Tom

 

[SBendBuckeye]

Is product in the order table = productnumber in the product table? Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

Yes, product in the order table = productnumber in the product table.

Product Table: Order Table:
Product_ID: 16 Order_ID: 129
ProductNumber: 543 Product: 543

 

[SBendBuckeye]

You may still be OK then.

1. Open your database and go to tools and select relationships.

2. Select your product and order tables similarly to the way you select tables when building a query in design view.

3. Left click and hold ProductNumber in the Product table and drag the mouse over Product in the Order table. You always want to start from the 1 side of the 1 to many relationships.

4. Check the Enforce Referential Integrity checkbox

5. Check the Cascade Update Related Fields checkbox which becomes active once you have completed Step 4. When you make changes to ProductNumber they will automatically be reflected in Product because you defined a linkage.

6. Check the Cascade Delete Related Records checkbox. This will automatically delete Order records if the Product record is deleted.

If you can go back, you should redo your table design as follows:

1. Create a field Product_ID as a Foreign Key in the Order Table.

2. Populate your new field in the Order table using an update query or SQL. I hosed my Access so I can't give you actual code right now but the basic syntax for the join would be FROM Order INNER JOIN Product ON Order.Product = Product.ProductNumber.

3. Of course, you will test this heavily on copies of your database.

4. Whenever I have to do something like this, I usually write a query to verify my results along the line of SELECT Product.Product_ID, Order.Product_ID, Product.ProductNumber, Order.Product FROM Product INNER JOIN Order ON Product.Product_ID <> Order.Product_ID. If things went correctly, this query should return 0 rows. Just to be safe, I then change the INNER JOIN to = and verify that it returns all rows.

5. Once you have verified all of the above, set a 1 to many relationship between the Product_IDs as we discussed above.

6. Once the relationship is established, you can then delete Product from your Order table and link it in via the new field we created.

7. I understand that your ability to do the second case depends on many factors, some of which may be out of your control.

Hope this helps get you started. I don't know if I'll be able to get back on this weekend, but I'll try. I'm sure there are others that will pick up the slack.

Good Luck!

Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[SBendBuckeye]

Not sure of your comfort level here, but also don't forget your local Public library as a resource. When I first started I checked out several books which included CDs with samples to use and learn from.

Good Luck! Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

 

[TSimonick]

Thanks very, very much

Your procedure worked perfectly! I didn't understand the use of the Update query. The examples in the books (see I have a couple) is just multiplying a number like you are increasing a price. So I would have totally missed the way you used it.

Regards,

Tom