sum in a query

[everwhat]

I have created query which I pulled some fields from three tables that are link together. In the design view I’ve created some extra fields to sum and place amounts based on my criteria. See below:

Future: Sum(IIf([GL]![Record Type]="Future",[GL]![Amount]))

Budget: Sum(IIf([GL]![Record Type]="Budget",[GL]!Amount))

Reclass: Sum(IIf([GL]![Record Type]="Reclass",[GL]!Amount))

Assumption: Sum(IIf([GL]![Record Type]="Assumption",[GL]!Amount))

Amts: Sum(IIf([GL]![Record Type]="Amts",[GL]!Amount

Total: Sum(IIf([GL]![Record Type]="Amts" Or [GL]![Record Type]="Assumption" Or [GL]![Record Type]="Budget" Or [GL]![Record Type]="Reclass",[GL]!Amount))

I have created another field to subtract Future from Total, ( see below) but the amounts will not populate when I run the query.
Do you have any suggestions?

Var: [Future]-[Total]

 

[VicRauch]

You have only provided a value for the True case in the IIf statement. You need to add a ", 0" to each IIf statement above.
Also, you stated you were subtracting Future from Total, but then what you showed was Var: Future - Total which is subtracting Total from Future. Which one is what you want?
HTH,

 

[everwhat]

I had misstated myself, I want to subtract Total from Future.

Could you take one of my statement, such as the one below and tell me how I would place the zero?
Total: Sum(IIf([GL]![Record Type]="Amts" Or [GL]![Record Type]="Assumption" Or [GL]![Record Type]="Budget" Or [GL]![Record Type]="Reclass",[GL]!Amount))

 

[everwhat]

I had misstated myself, I want to subtract Total from Future.

Could you take one of my statement, such as the one below and tell me how I would place the zero?
Total: Sum(IIf([GL]![Record Type]="Amts" Or [GL]![Record Type]="Assumption" Or [GL]![Record Type]="Budget" Or [GL]![Record Type]="Reclass",[GL]!Amount))

 

[VicRauch]

Assumption: Sum(IIf([GL]![Record Type]="Assumption",[GL]!Amount, 0))

The syntax for the IIf statement is in the Help file with Access. It is always a good idea to review the Help topic when working with something new, or when it does not work correctly.

 

[SydneyGeek]

Building on what Vic said...

Get the first set of calculation fields working. Then --

Your field [Total] - [Future] will not work in this query, because Access doesn't store intermediate calculations. You will need to write it with the full expressions that calculated Total and Future, like:

Sum(IIf([GL]![Record Type]="Amts" Or [GL]![Record Type]="Assumption" Or [GL]![Record Type]="Budget" Or [GL]![Record Type]="Reclass",[GL]!Amount),0) - Sum(IIf([GL]![Record Type]="Future",[GL]![Amount]),0)

Denis