Update Query

[Guest]

I want to update the following info from one table to another

Table: Orders
Field Names:
Name
Phone Number
Order Number

Table: Updates
Field Names:
Ord #
PHN #

The Order Number and the ORD # are the same and the relationships (Join
Properties) are set to only include rows where the joined fields from both
tables are equal.

What i want to do is, if the Ord # from the Updates table matches the Order
Number from the Orders table then put the info that is in the PHN # (from the
Updates table) and put it in the Phone Number field in the Orders table.

It seems pretty easy but I can't figure it out.
Question 1: What type of Query would I use. ie Update query, or append query
Question 2: What info goes in the folllowing in the query
Field:
Table:
Update to:
Criteria:

Thanks, Sean

 

[John Vinson]

Answers inline

I want to update the following info from one table to another

Table: Orders
Field Names:
Name

Name is a reserverd word, and it's risky as a fieldname. You might
want to change this to CustName.

Phone Number
Order Number

Is Order Number the Primary Key of the table? The update query won't
work unless it has a unique Index (such as a primary key).

Table: Updates
Field Names:
Ord #
PHN #

Again, the # character is a bit risky in fieldnames - it's a date
delimiter. If you use any special characters (including blanks) or
reserved words in fieldnames, you should - must! - consistantly use
[Square brackets] to delimit the fieldnames.

The Order Number and the ORD # are the same and the relationships (Join
Properties) are set to only include rows where the joined fields from both
tables are equal.

What i want to do is, if the Ord # from the Updates table matches the Order
Number from the Orders table then put the info that is in the PHN # (from the
Updates table) and put it in the Phone Number field in the Orders table.

It seems pretty easy but I can't figure it out.
Question 1: What type of Query would I use. ie Update query, or append query

An Update query updates existing records. An Append query adds new
records which did not exist previously; so in this case it's an Update
query.

Question 2: What info goes in the folllowing in the query
Field:
Table:
Update to:
Criteria:

Create a Query by adding Orders and Updates to the query window.
Access should automatically put in a line joining Ord # to Order
Number. Select the [Phone Number] field that you want to update.
Change the query to an Update query using the query type icon; on the
Update To row put

[Updates].[PHN #]

This specifies the table and field - the brackets are essential here
even if your fieldnames are purely alphanumeric; if you were to update
the field to

Updates.PhoneNumber

you'ld get the words Updates.PhoneNumber in the field! The brackets
tell Access that you want to update from the content of the field.

You may want to put a criterion of

IS NULL

on the Phone Number field if you want to add only new phone numbers;
if you don't then any existing phone number will be blindly
overwritten with the Updates table value (which might be exactly what
you want).

Run the query by clicking the ! icon.


John W. Vinson[MVP]