Price Update

[RussG]

Hi - just lookign for a little assistance with an update problem. Not sure if this can be done with a query or if I will need to use VBA.

Access 2003
I have 3 tables;
Orders
Products
ProductOrders

On my Orders form the ProductOrders are in a subform which uses a Dlookup to insert the unit price when each product is selected ( standard stuff)

There is an annual % price rise which I manage by updating the prices in the Product table and running an Update query
on all ProductOrders >= to the price increase date.

This works well but this year the prices are not a consistent % so after updating the Products table prices I need to somehow trigger a new DLookup on the ProductOrders that are >= to the price increase date.

I suspect I will need to open a recordset and loop through it but need a little help to get started.

Any assistance will be much appreciated.

RussG

 

[Steve R.]

This works well but this year the prices are not a consistent % so after updating the Products table prices I need to somehow trigger a new DLookup on the ProductOrders that are >= to the price increase date.

I won't be able to give you any SQL advice.

Since the price increases are not a consistent % and seem to have a date dependency, you may want to create a data entry form that uses the product list in a combo box where you can add the price increase and the effective date. Of course this may mean some modifications to your underlying tables. But that should address both variable rate price increases and the date of implementation.

 

[RussG]

The solution came to me overnight and its simple.
I just needed to use an update query by referencing the updated table.
The ' Update To ' criteria is [tblProducts].[Prices].

I was over thinking this one.

Anyway thanks Steve for your suggestion.