Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 285

# query

I have an Access file, which has 3 tables, A, B, C

Table A has fields: Store SKU #, name of the product, people who ordered them, Quantities in stock, promotional price, store price factor.

Table B has fields: Store SKU #, UPC code, standard prduct description

Table C has fields: UPC code, Standard pricing

Table A and B has a relationship, by linking Store SKU #, the joint type is that contain all the records from Table A and same records from Table B. It produce a query D, this D contain fields Store SKU #, name of the product, people who ordered them, Quantities in stock, UPC code, standard prduct description, promotional price,

Now I need a query, which will be able to do the following.
If the Quantity in stock field in Query D is between 1 to 999, it uses, for example, promotional pricing* 0.75
If the Quantity in stock field in Query D is between 1000 to 1999, it uses the standard pricing from Table C.
If the Quantity in stock field in Query D is between 2000 to 2999, it will uses Standard pricing from Table C *1.2
If the Quantity in stock field in Query D is between 3000 to 3999, it will uses 123*store price factor

How can we produce this new query?

0
wsjwsj2
1 Solution

Commented:
You can use an IIF() function.  This function has 3 parameters: condition to test, value to return if true, value to return if false:

iif([Quantities in stock] < 999 , [promotional pricing] * 0.75, iif([Quantities in stock] < 1999, [Standard price], iif([Quantities in stock] < 2999, [Standard price], 123 * [Store price factor])))

0

Commented:
Forced accept.

Computer101
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.