Getting Sums from Grouping Footers 1

[Guest_imported]

I work at a car dealership and am building a database to show commissions being paid to the salespeople. However, they don't get paid until a car has been "funded" by a bank. I want my report to show all the commissions being paid for the week plus the sales that haven't been funded. The problem lies in that I want to calculate a grand total of only the commissions being paid and not of the non-funded sales, but I still want to show them all. I grouped the report by salesperson, then by whether a sale was funded or not. So, I got a way to show the earnings for each salesperson in the Funded Footer by using the formula (FUNDED is a checkbox):

=IIf([FUNDED]=True,Sum([COMMISSION PAID]),0)

This is fine, but it displays 2 "earnings" for each salesperson. The first has the sum of the money from the funded deals and the second is 0 from the unfunded deals. I would appreciate any help on removing the "Earnings=0", but my bigger problem is that I cannot get a Grand Total at the end of the report. It gives me the sum of all the sales whether funded or not, which does not give an accurate representation of what we actually pay out. Any suggestions. Thanks and sorry for being so wordy.

 

[CTOROCK]

you can have a query that only lists the funded and sum them up there and call that field something else, then rejoin that field with the rest in another query
"The greatest risk, is not taking one."

 

[GoDawgs]

=Dsum("[commission paid]", "the table name", "[FUNDED]=True&quot

That should work...subsitute the name of your table in, but make sure you still put the quotation marks around it.

As for you other problem, the only reason that the "0" is coming up is because you are telling it to print 0 when funded doesn't equal true...make sense? So just take that out and nothing will show up...and to take that a step further you might want to do this:

=IIf([FUNDED]=True,"Earnings = " & Sum([COMMISSION PAID]))

That way it will either come up with earnings or just be totally blank...Hope this helps.

Kevin

 

[Guest_imported]

Thanks Kevin. Your suggestions worked great. The only thing is that I want the formula :

=IIf([FUNDED]=True,"Earnings = " & Sum([COMMISSION PAID]))

to bring back "Earnings = ####.##" with 2 decimal places. However, if I don't change the format, then the Earnings= is converted to a number. If I do change the format, then the number doesn't round. I think it involves a Format$ or Format(other special character tag), but I am not sure what to do exactly. Thanks in advance.

 

[GoDawgs]

put the format around the sum...so something like this:

=IIf([FUNDED]=True,"Earnings = " & Format(Sum([COMMISSION PAID]),####.##))

That's not right, I honestly can't remember how to set up Format...but it's in help somewhere. Glad I could help.

Kevin