Prev: Need to show result in message box
Next: Filter form using a textbox
From: Phil Smith on 1 Feb 2010 18:39
I know this has to come up a lot, and I am wondering the best way to
handle it.

I have two tables in a one to many relationship. Say an Product table,
and a price table, which will have as many as ten different prices.

I want to make sure that all items have a "Senior Discount price" of 10%
over product.cost.

The problem is that some of these products will have a "Senior Discount"
of some value, which of course I want to change to 10%, and other
products will not have a 'Senior Discount" price. What is the fastest
way to both update the existing prices, and add new prices where neccessary?

From: pietlinden on 1 Feb 2010 20:50
On Feb 1, 5:39 pm, Phil Smith <p...(a)nhs-inc.com> wrote:
> I know this has to come up a lot, and I am wondering the best way to
> handle it.
>
> I have two tables in a one to many relationship.  Say an Product table,
> and a price table, which will have as many as ten different prices.
>
> I want to make sure that all items have a "Senior Discount price" of 10%
> over product.cost.
>
> The problem is that some of these products will have a "Senior Discount"
> of some value, which of course I want to change to 10%, and other
> products will not have a 'Senior Discount" price.  What is the fastest
> way to both update the existing prices, and add new prices where neccessary?

I guess you have to store the new price because it's essentially a new
fact... one of the few exceptions to the "do not store derived data"
rules. I would run the update query first, and then turn the result
of the find unmatched query wizard into an append query. Then you
could just run the two queries in sequence.
 | 
Pages: 1
Prev: Need to show result in message box
Next: Filter form using a textbox