sum in a query

everwhat

Board Regular
Joined
Jul 23, 2002
Messages
189
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]
 

Some videos you may like

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
Joined
Mar 23, 2006
Messages
2,032
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
Joined
Jul 23, 2002
Messages
189
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
Joined
Jul 23, 2002
Messages
189
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
Joined
Mar 23, 2006
Messages
2,032
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
Joined
Aug 5, 2003
Messages
12,251
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,707
Messages
5,524,424
Members
409,577
Latest member
Dwg

This Week's Hot Topics

Top