How to UPDATE a field in a joined table?

[bws93222]

I have a table "Orders" with a foreign key called ProductID (which is joined
to my "Products" table which lists the ProductDescription text. I want to do
an UPDATE query to update a Product(ID) from an order in my orders table.
But ACCESS's query grid seems to only allow me to do the update by entering
the numerical ProductID rather than the ProductDescription text. Surely
there must be an easy way to do an update the Orders table by refering to
just the ProductionDescription...can you please suggest how to do this? Thx

 

[KARL DEWEY]

I assume that your Products have a relationship of one-to-many with the
Orders on Product(ID) with referential integerity.
Which field are you wanting to update - ProductID or ProductDescription? -
in which table?
Post the SQL of your query.

 

[Roger Carlson]

If I'm reading this correctly, it sounds as if you are using a Lookup Field
as your foreign key. This is usually a bad idea. See this link:
http://www.mvps.org/access/lookupfields.htm

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[bws93222]

Yes..it's 1-to-many w/ ref integrity. Here's what I want to do:

1) I run a SELECT query showing all fields from my OrdersTable (containing
foreign key: ProductIDNumeric) and the associated fields from the joined
ProductsTable (containing primary key: ProductIDNumeric and
ProductDescriptionText). Results are successfully displayed showing many
orders Example: 100 order records with ProductID Numeric value: 1001,
ProductDescriptionText value: 'Widget', and 25 order records with
ProductIDNumeric value: 2001, ProductDescriptionText value: 'DooDad'.

2) But after reviewing the displayed results, I want to run an UPDATE query
to update some of the OrdersTable.ProductIDNumeric values to a different
value (ex: I want modify 10 of the DooDad orders to become Widget orders).
In my Access2007 query grid, I obviously need to update the "Update to" field
under OrdersTable.ProductIDNumeric to the updated numeric value that
corresponds to 'Widget' in order to create the query properly. I could
simply enter '1001' and that would work. But what if I don't happen to
remember this number and can only recall the more meaningfull
ProductDescriptionText? Is there a drop down or a wizard that can pull this
info for me and populate the appropriate value into the query grid.?

Thx.


bws93222

 

[bws93222]

(no lookup fields are used in the tables--just the straight numeric foreign
key values)

 

[bws93222]

Here's the SQL which works--however as you can see it uses
the Product_ID field only instead of a ProductDescription field to do
the update to the OrdersTable. I admit this is what needs to happen
'behind the scenes', but what I want is a more user friendly
way to accomplish the same thing except by using the ProductDescription.

UPDATE ProductsTable INNER JOIN OrdersTable ON ProductsTable.[Product_ID] =
OrdersTable.[ProductID]
SET OrdersTable.Product_ID = 1001
WHERE (((OrdersTable.ProductID)=2001));

 

[KARL DEWEY]

I do believe you need to use a left join. I never heard of a drop down for
an update query. A drop down can display multiple fields while it is
expanded and while you are looking to make your selection. You could make
changes to the orders one at a time in your form/suform - Product/Orders.
Which 10? Your query will update ALL of them.

Here's the SQL which works--however as you can see it uses
the Product_ID field only instead of a ProductDescription field to do
the update to the OrdersTable. I admit this is what needs to happen
'behind the scenes', but what I want is a more user friendly
way to accomplish the same thing except by using the ProductDescription.

UPDATE ProductsTable INNER JOIN OrdersTable ON ProductsTable.[Product_ID] =
OrdersTable.[ProductID]
SET OrdersTable.Product_ID = 1001
WHERE (((OrdersTable.ProductID)=2001));

--
bws93222

Yes..it's 1-to-many w/ ref integrity. Here's what I want to do:

1) I run a SELECT query showing all fields from my OrdersTable (containing
foreign key: ProductIDNumeric) and the associated fields from the joined
ProductsTable (containing primary key: ProductIDNumeric and
ProductDescriptionText). Results are successfully displayed showing many
orders Example: 100 order records with ProductID Numeric value: 1001,
ProductDescriptionText value: 'Widget', and 25 order records with
ProductIDNumeric value: 2001, ProductDescriptionText value: 'DooDad'.

2) But after reviewing the displayed results, I want to run an UPDATE query
to update some of the OrdersTable.ProductIDNumeric values to a different
value (ex: I want modify 10 of the DooDad orders to become Widget orders).
In my Access2007 query grid, I obviously need to update the "Update to" field
under OrdersTable.ProductIDNumeric to the updated numeric value that
corresponds to 'Widget' in order to create the query properly. I could
simply enter '1001' and that would work. But what if I don't happen to
remember this number and can only recall the more meaningfull
ProductDescriptionText? Is there a drop down or a wizard that can pull this
info for me and populate the appropriate value into the query grid.?

Thx.


bws93222

 

[John W. Vinson]

I have a table "Orders" with a foreign key called ProductID (which is joined
to my "Products" table which lists the ProductDescription text. I want to do
an UPDATE query to update a Product(ID) from an order in my orders table.
But ACCESS's query grid seems to only allow me to do the update by entering
the numerical ProductID rather than the ProductDescription text. Surely
there must be an easy way to do an update the Orders table by refering to
just the ProductionDescription...can you please suggest how to do this? Thx

You can use a Form to help with this. You do indeed need to update the table
using the ID (if you want to do this AT ALL - surely each order might have
more than one product!?) then you can simply bind a combo box to the ProductID
field. The combo can store the productID while displaying the product name.

If you do need to do it with an update query, launch the query from a command
button on a Form containing an unbound combo box based on the product table.
It should have its bound column as the ProductID and display the product
description; the update query would update the field to

=Forms![YourFormName]![YourComboboxName]

 

[bws93222]

Thx everyone for your helpful replies. (John, I accidentally clicked 'not
helpful' when I meant to click 'helpful' on your post--if you can tell me how
to uno it, I will. ) I guess I shouldn't always assume Access will provide
an easy shortcut for all tasks.
--
bws93222

I have a table "Orders" with a foreign key called ProductID (which is joined
to my "Products" table which lists the ProductDescription text. I want to do
an UPDATE query to update a Product(ID) from an order in my orders table.
But ACCESS's query grid seems to only allow me to do the update by entering
the numerical ProductID rather than the ProductDescription text. Surely
there must be an easy way to do an update the Orders table by refering to
just the ProductionDescription...can you please suggest how to do this? Thx

You can use a Form to help with this. You do indeed need to update the table
using the ID (if you want to do this AT ALL - surely each order might have
more than one product!?) then you can simply bind a combo box to the ProductID
field. The combo can store the productID while displaying the product name.

If you do need to do it with an update query, launch the query from a command
button on a Form containing an unbound combo box based on the product table.
It should have its bound column as the ProductID and display the product
description; the update query would update the field to

=Forms![YourFormName]![YourComboboxName]