Calculate percentages in a report -With sample database

[greekneon]

Summary By account – Report



When the database opens you seen the View reports window
Select the “Summary by account type” and if you
Only show transactions of this range eg 1/7/12 to 1/13/12 you should get the following report
_____________________________-
Summary by Account Type Tuesday, May 01, 2012 8:32:20 PM
For Transactions Between: 1/7/2012 and 1/13/2012
Account Type Account Number Account Name W/D Amt Deposit Amt Balance
Foodcost
016 Bread $194.00
010 Soda $621.82
620 Vendor x $1,450.45
640 vendor xyz $279.63
620 vendor y $335.98
(5 detail records)
Sum $2,881.88
Marketing
008 Marketing $506.50
008 Marketing $35.00
(2 detail records)
Sum $541.50
Overhead
017 A&B $1,500.00
015 Accountant $100.00
012 CO2 Tank $75.00
009 Filter $123.50
011 Maintanance $75.00
013 Tech Support $80.00
014 Trash Removal $205.13
(7 detail records)
Sum $2,158.63
Payroll
430 Payroll $2,512.21
(1 detail record)
Sum $2,512.21
-Sales
001 Sales $9,352.74
(1 detail record)
Sum $9,352.74
Grand $8,094.22 $9,352.74
Page 1 of 1



I would love to have percentages next to the totals but I don’t know how to. Any ideas?

1. percentages over gross sales
   
   1. Foodcost $2,881.88/9352.74 = 30.8%
   2. Marketing 541.50/9352.74 = 5.6%
   3. Overhead section $2,158.63/9352.74= 23.1% etc etc etc

1. Also "Balance" doesn’t work. At the end of the report you get the withdrawals’ and deposit sums but the subtraction of the both is not working.

Sample.zip

[pbaldy]

Percentages this type of thing:

=Sum([WithdrawalAmount])/[DepositAmount Grand Total Sum]

Balance would need to include the Nz() function to account for Nulls:

=Sum(nz([DepositAmount],0)-nz([WithdrawalAmount],0))

[greekneon]

Thank you so much. Works great!!! At the bottom i would like to have to display the grand total for foodcost, or overhead, how do i select a specific account type, something like =Sum([WithdrawalAmount Grand Total Sum Account type "Foodcost"]. What i would to do at the end of the report is get for example foodcost % payroll % and AllOther %(All other accounts added together minus the profit distribution account)

I tried a couple things =Sum[Foodcost] with running sum over the group but nothing works

Any Ideas?

[pbaldy]

Well, for a single one or two you can do this:

=Sum(IIf(AccountType = "Foodcost", AmountField, 0))

If you want them all summed, I'd probably use a subreport based on a totals query.

[greekneon]

Thank you it worked great again, The form Accounts has the same problem though like the report it doesn't add up the withdrawals. the form "accounts" has a subform "account subform" and in form footer it adds the withdrawals =nz(Sum([WithdrawalAmount])) i also tried =Sum[WithdrawalAmount] and =Sum(nz([DepositAmount],0) but it doesn't give me the total at the bottom

Any Ideas?

[pbaldy]

The subform is in datasheet view, so it won't show the footer. Try changing it to continuous. By the way, you want:

=Sum(nz([DepositAmount],0))

[greekneon]

Thank you again, it worked great, I used =nz(Sum([WithdrawalAmount])) and it works so i didn't bother with it. I'm currently trying to create query to do those calculations but no luck so far. work in progress

[pbaldy]

No problem, and welcome to the site by the way.