Simple Query Qeuestion

[robin123]

I have two tables -

Table1(ItemID, Quantity)
Table2(ItemID, Quantity)

I want to run a query so that if i enter a value for quantity in table 2 that is less than the value in table 1 that it will take the value from table 2 and replace the value in table 1 with this value.

How do I do this?

Thanks

 

[KjWhal]

I have two tables -

Table1(ItemID, Quantity)
Table2(ItemID, Quantity)

I want to run a query so that if i enter a value for quantity in table 2 that is less than the value in table 1 that it will take the value from table 2 and replace the value in table 1 with this value.

How do I do this?

Thanks

UPDATE Table1
SET Table1.Quantity = 
(SELECT Table2.Quantity
 FROM Table1,Table2
 WHERE Table1.Quantity > Table2.Quantity)

Should do it.

 

[robin123]

Whenever I run that an error message comes up which says -
'Operation must use an updateable query'
Any ideas?

 

[KjWhal]

Whenever I run that an error message comes up which says -
'Operation must use an updateable query'
Any ideas?

Google returns a lot of results..this seems most likely, but having not seen your database I have no idea, sorry.

http://support.microsoft.com/?id=175168

The last issue and work around pertains to any SQL data source. The error can be caused by SQL statements that violate referential integrity of the database. Here are a few of the most common queries that fail:
• The simplest groups to deal with are those you cannot change: crosstab, SQL pass-through, union, or update (or make-table) action queries that have UniqueValue properties set to Yes.

• Another very common cause is when the join includes linked ODBC tables that do not have unique indexes. In this case, there is no way for SQL to guarantee that records are unique in a table that has fields whose value will change with the query.

• One cause does have a robust workaround. If you try to update a join field on the "one" side of a "one-to-many" query it will fail unless you turn on cascading updates. This way, you delegate referential integrity to the JET engine.