Sum of sums

  • Thread starter Thread starter MilesMonkMingus
  • Start date 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
 
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
 
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.
 
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...
 
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...
 
Back
Top