# sum in a query

#### everwhat

##### Board Regular
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]

### Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

#### VicRauch

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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

##### Well-known Member
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

##### MrExcel MVP
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

Replies
14
Views
238
Replies
5
Views
969
Replies
7
Views
1K
Replies
1
Views
541
Replies
9
Views
354