Calculated Field

[Dion]

I am running a query on a table called tblTransactions.
The fields are as follows:
Transaction ID
Transaction Date
Customer Name
Settlement Date
Number of Transactions
File Total

I am running a query on the fields:
Customer Name
Number of Transactions

I want a calculated field called Monthly Bill. Based on a
running total of the number of transactions, I want the
following results:

If Number of Transactions are <=3, then Monthly Bill= Sum
(Number of Transactions)*16.00 else
If SumNumber of Transactions are Between 4 And 6, then
Monthly Bill= 54 else
If SumNumber of Transactions are >=7, then Monthly Bill=
98.00.

What is the expression in the calculated field? I can't
seem to get the correct syntax for it. Any and all help
is appreciated.

 

[Guest]

You said it : IF
More accuratly IIF (immediate if) look it up
Syntax is IIF(condition, then-value, else-value)

you can nest them (replaceing else-value with another IIF statement

somthing lik
MonthlyBill:IIF(Number of Transactions <4, (Number of Transactions*16), IIF(Number of Transactions< 7, 54, 98)

One thing: you can't do this in the same query where you add up number of trans, you'll have to make a query based on that query (a two stage query!