Sum of sums

  • Thread starter MilesMonkMingus
  • Start date
M

MilesMonkMingus

I am attempting to sum various sums that are conditional. Basically, sum A + B, where A is:
SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0)) AS YTD_PENHW1

and B is:

SUM(IIF([PCPCAJCD2]="HC ",PCPYAJAM1,0)) AS YTD_PENHW2

I've attempted to do this, but to no avail:

SUM([SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0))] + [SUM(IIF([PCPCAJCD2]="HC ",PCPYAJAM1,0))]) AS YTD_PENHW
 
D

Duane Hookom

Try:
SUM(IIF([PCPCAJCD1]="HC " OR [PCPCAJCD2]="HC ",PCPYAJAM1,0)) AS YTD_PENHW

--
Duane Hookom
MS Access MVP
--

I am attempting to sum various sums that are conditional. Basically, sum A +
B, where A is:
SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0)) AS YTD_PENHW1

and B is:

SUM(IIF([PCPCAJCD2]="HC ",PCPYAJAM1,0)) AS YTD_PENHW2

I've attempted to do this, but to no avail:

SUM([SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0))] + [SUM(IIF([PCPCAJCD2]="HC
",PCPYAJAM1,0))]) AS YTD_PENHW
 
O

Ofer

Another option will be to create two queries, first one to create the first
sum, and then create another query based on the first query, that sum the sum
fields.
 
M

MilesMonkMingus

This is an interesting solution. However, I'm a novice--can you lend me
some direction to how I can write a second query that accesses the results
of the previous query?

M

Ofer said:
Another option will be to create two queries, first one to create the
first
sum, and then create another query based on the first query, that sum the
sum
fields.
--
I hope that helped
Good luck


MilesMonkMingus said:
I am attempting to sum various sums that are conditional. Basically, sum
A + B, where A is:
SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0)) AS YTD_PENHW1

and B is:

SUM(IIF([PCPCAJCD2]="HC ",PCPYAJAM1,0)) AS YTD_PENHW2

I've attempted to do this, but to no avail:

SUM([SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0))] + [SUM(IIF([PCPCAJCD2]="HC
",PCPYAJAM1,0))]) AS YTD_PENHW
Click to expand...
Click to expand...
 
M

MilesMonkMingus

Ofer,

Your solution works like a champ! Working w/ the 'design view' I was able to
make my way though the darkness. Thanks for your solution.

M

Ofer said:
Another option will be to create two queries, first one to create the
first
sum, and then create another query based on the first query, that sum the
sum
fields.
--
I hope that helped
Good luck


MilesMonkMingus said:
I am attempting to sum various sums that are conditional. Basically, sum
A + B, where A is:
SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0)) AS YTD_PENHW1

and B is:

SUM(IIF([PCPCAJCD2]="HC ",PCPYAJAM1,0)) AS YTD_PENHW2

I've attempted to do this, but to no avail:

SUM([SUM(IIF([PCPCAJCD1]="HC ",PCPYAJAM1,0))] + [SUM(IIF([PCPCAJCD2]="HC
",PCPYAJAM1,0))]) AS YTD_PENHW
Click to expand...
Click to expand...
 
Top