Calculation...Is this a possibility?

[Tia]

I'm just trying to brainstorm a solution to this situation. Any suggestions
would be VERY helpful.

I currently have a report that's datasource is a query. The report displays
a price sheet for each of our customers. The query has a field that
calculates the price of product by the vendorcost * by the markup (both are
fields in the query). This works great. However, not all products have the
same markup. I would like some type of "if/then" statement that says okay if
the product is not "x,y or z", then multiply the vendorcost by the markup.
However, if the product is "a" then multiply the vendorcost by D, if "b"
multiply the vendorcost by F, if "c" then multiply vendor cost by G .

Is this a possibility, if so, what is the best way to go about it??

 

[DavisGail]

Tia,
Do you have a "products" table that you can add a field to that has the
markup value? If you do, or if you can add that, you can pull that into your
query to do your calculation. You mentioned that you have a "markup" in your
query now...where is that pulling from?

 

[Tia]

I do have a products table. However, in "most" cases the markup is based on
the customer. So the "markup field" is in my customer table. We have a few
instances where the standard markup for a customer needs to be adjusted based
on a product. So I really want my query to use the markup based on the
customer unless it is one of the designated products that has a unique
markup.

 

[DavisGail]

I would add an additional "unique markup" field to the Products table. Only
fill in the the Unique markup field of the product if it has one. That way
you can create a field in your query called "price of product" with the
following:

Price of Product: IIf([Unique
Markup]=Null,[VendorCost]*[StandardMarkup],[VendorCost]*[Unique Markup])