Solved

# query

Posted on 2007-10-17
271 Views
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
Question by:wsjwsj2

LVL 14

Accepted Solution

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

LVL 1

Expert Comment

Forced accept.

Computer101
0

## Featured Post

### Suggested Solutions

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…