Complex Grouping query

[Chris Hyatt]

Hello:

Please help me with the following:

I have a table with invoice information. There is a Currency flag(Local or
foreign currency). There is also flag for Taxable or Non-Taxable. I want
to group the records, summing them while taking the flags into
consideration. For example:

If the NO Tax flag is checked, then sum it without the tax, or if the
Currency flag is checked do something.

id charge curr tax? tax rate
11 $499 f f .05
11 $598 f f .05

the result would be (Grouped by id)
id charge curr tax? tax rate
11 $1097 t f .05

------------------------------------------------------

The tax flag is different so the query would apply the tax rate
id charge curr tax? tax rate
11 $499 t t .05
11 $598 t t .05

the result would be (Grouped by id)
id charge curr tax? tax rate
11 $1151.85 t f .05

I envision it to look like this(PseudoCode):
SELECT table.ID, If table.tax=t then (Sum(table.Charge)* 1.05) AS NewCharge
else (Sum(table.Charge)FROM table GROUP BY table.ID;

Any help would be greatly appreciated. Thanks.

 

[John Spencer (MVP)]

Use the immediate if function.

SELECT Table.id,
Sum(IIF([Tax] = "t",[Tax Rate]* [Charge],[Charge])) as TotalCharge
FROM Table
Group By Table.ID