By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

ACCESS 2007: Updating Multiple tables

P: 16
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
Nov 29 '11 #1

✓ answered by TheSmileyCoder

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

Expand|Select|Wrap|Line Numbers
  1. UPDATE Director INNER JOIN Order_Summary 
  2.   ON     Director.[name] = Order_Summary.[name] 
  3.   SET    Director.[name] = "NewName", 
  4.          Order_Summary.[name] = "NewName"
  5.   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

Share this Question
Share on Google+
3 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
You can run an update query to update both fields at the same time.

Expand|Select|Wrap|Line Numbers
  1. UPDATE Director INNER JOIN Order_Summary 
  2.   ON     Director.[name] = Order_Summary.[name] 
  3.   SET    Director.[name] = "NewName", 
  4.          Order_Summary.[name] = "NewName"
  5.   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
Nov 29 '11 #2

P: 16
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
Nov 30 '11 #3

NeoPa
Expert Mod 15k+
P: 31,186
Happy holidays to you too Kendall, but please remember to select Smiley's post as Best Answer before you start :-)
Nov 30 '11 #4

Post your reply

Sign in to post your reply or Sign up for a free account.