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]
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

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
 

Forum statistics

Threads
1,141,221
Messages
5,705,100
Members
421,378
Latest member
CarlosDuran

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top