sum of columns dependant on other form answers

[pshawk]

i have a table and form set up for tracking requests for money. each person can request 5 times (on the same form) and each time the money may come from one of three accounts. the table is set up with value lists for the account names. so there is columns for

account 1
amount 1
account 2
amount 2


etc

i need to generate a report that sums the amount that has been requested of each account. i am at a loss on how to make this happen.

i know the way to say it is

Sum "amount 1" if account = "account name" + sum "amount 2" if account = "account name"

[llkhoutx]

Sum "amount 1" if account = "account name" + sum "amount 2" if account =  "account name" 

makes no sense.

Build the requisite value in an additional column using a conditional IIF if you recordsource (I assume you have a report); sum that column.

[pshawk]

thank you for replying, what would that look like? do i make a query with all of the fields and then a report off of the query?

it has been a long time since i used access and i need it spelled out for me, thanks

[llkhoutx]

It's a nested conditional iff.

iif(condition1,sum1,iif(condition2,sum2,"")) 

[pshawk]

do i need to bracket or quote the conditions

[pshawk]

do i do this in the report design view, if so is it in the footer or the detail.

[pshawk]

i kept google-ing and finally was able to figure it out

sum(iif([fund 1]="soc",[amount 1],0)+iif([fund 2]="soc",[amount 2],0))

luckily i figured out a better way to organize my tables so that there is a one to many relationship and now i dont have to repete the logic over and over again