HOT problem - Update a particular record with update query

[Mich2too]

Working with two tables, one is an order table and the other is a customer table. When entering a new order, want to run an update query to copy the proper customer information into the order table. Am having problems getting the one particular record to be updated.

Here's the SQL from SQL view of query:

UPDATE OurTripNumber INNER JOIN Customer ON OurTripNumber.CustomerID = Customer.cID SET OurTripNumber.CustomerID = [customer].[cid], OurTripNumber.Name = [customer].[cname], OurTripNumber.Address = [customer].[caddress], OurTripNumber.City = [customer].[ccity], OurTripNumber.State = [customer].[abbreviation], OurTripNumber.Zip = [customer].[czippostal], OurTripNumber.Tel = [customer].[cphone]
WHERE (((OurTripNumber.CustomerID)=[Update with what Customer?]) AND ((OurTripNumber.OrderNumber)=[Update what order #?]));

Am not overly familiar with SQL. Thanks in advance for any help.

 

[Malchik]

hi,

Why are you duplicating the customer information. I guess when you have a new order, you can insert in Order table the Customer.cID and the rest of the order... Mal'chik

 

[Quehay]

You're trying to denormalize with a trigger-like functionality here. But why, unless you're trying to create a portable flat file? But if you create a view (query) that presents the joined tables (join on CusID) then you've got something that can be inserted as values in a temp table or Excel easily.

The problem with denormalized data is that you'll get an "Update Anomaly" if an attribute of a customer changes, and you don't programmatically update every corresponding field in the orders table. Jeff Roberts
Analysis, Design, & Implementation
RenaissanceData.com

 

[Mich2too]

Yeah...had the same feelings about duplicating information, but am working with tables that were created by a different program and imported into Access database. Both tables had the information in them and was trying to keep the same flow going. Having others agreeing that duplication is silly gives me what I need to convince bossman to let me do it another way.

Can't thank you all enough!!

Sue