# 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 date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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,

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))

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))

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.

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
5
Views
563
Replies
5
Views
442
Replies
3
Views
633
Replies
4
Views
520
Replies
4
Views
498

1,218,497
Messages
6,142,823
Members
450,449
Latest member
Dave Carr QM

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back