Adding and updating records based on results of a query

[Alex Hemingway]

I have a table Products consisting of the following fields :-

ProductID, ProductName, PriceLevel, UnitPrice

and a query which returns all products with a specific PriceLevel.
What I would like to do is allow the user to copy all products at one
price level returned in the query and add them to the the Products
table with another price level. For example if the user chose 1 and 3,
it would select all products with PriceLevel 1 add them to the
Products table and update their PriceLevel to 3.

I am no expert when it comes to Access but my inclination tells me
that this would be done with code. I would like advice on the code /
query necessary to perform this operation.

Thanks in advance for your help

[Matthew Sullivan]

I think you could do it with just queries and some temp (or,
'working') tables.

Just run a query to copy all the items with Price '1' to a temp table
(after first clearing out the temp table).

Run another query to update the Price to '3'.

Run a third query to append the modified records back.
-Matt


On 22 Sep 2003 05:29:37 -0700, al************@granadamedia.com (Alex
Hemingway) wrote:

I have a table Products consisting of the following fields :-

ProductID, ProductName, PriceLevel, UnitPrice

and a query which returns all products with a specific PriceLevel.
What I would like to do is allow the user to copy all products at one
price level returned in the query and add them to the the Products
table with another price level. For example if the user chose 1 and 3,
it would select all products with PriceLevel 1 add them to the
Products table and update their PriceLevel to 3.

I am no expert when it comes to Access but my inclination tells me
that this would be done with code. I would like advice on the code /
query necessary to perform this operation.

Thanks in advance for your help