ACCESS 2007: Updating Multiple tables

[phillikl]

Good day,
Having a total brain fart on this and need some help.

Access 2007
2 Tables

Table1: Director (200 records)
Field1: Name
Field2: phone

Table2: Order_Summary (414,000 records)
Field1: Name
Field2: orders

Director!name to Order_summary!Name Relatioship

Desire:

Enter Company Name to Change (user enters name)VAR1.
Enter New Company Name (what ever company was renamed to)VAR2.

FIND VAR1 in Director!Name and Order_summary!Name and update (both tables)!name to equal VAR2.

The largest issue I am having; what is the best way to grab and modify the data in both tables. I know how to do it the sloppy way, but with this many records I fear it may bloat.

Appreciate your help!

Thanks again,

Kendall

[TheSmileyCoder]

You can run an update query to update both fields at the same time.

UPDATE Director INNER JOIN Order_Summary 
  ON     Director.[name] = Order_Summary.[name] 
  SET    Director.[name] = "NewName", 
         Order_Summary.[name] = "NewName"
  WHERE  Director.lng_ProjectNr="OldName"

Another option would be to open the relationship manager, and modify the relation. Make sure that "Cascading updates" is enabled for the join, and then go and update your record in the Director table.


Now that all that is said, you should avoid using the field name Name as that is a reserved word in Access and will only cause you weird errors down the road.

You also need to take a look at:
Database Normalisation And Table Structures

[phillikl]

Thanks TSC! That was the road I was thinking, just needed a reality check.

Understand all on the naming convention; just wanted to make the example as simple as possible.

Thanks again for your help,

Happy Holidays,
Kendall

[NeoPa]

Happy holidays to you too Kendall, but please remember to select Smiley's post as Best Answer before you start :-)