By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,488 Members | 2,545 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,488 IT Pros & Developers. It's quick & easy.

Calculation in a report

P: n/a
pat
I'm working on a report.

I have two categories (groups) with a field labeled "Amount Funded." I
want to create a subtotal column (for each group) that subtracts the
"Amount Funded" from a number ($60,000.00 and $140,000 respectively),
and a "grand available total" that subtracts the total "Amount Funded"
from $200,000.00. How do I build that expression so that I have a
running "funds available" subtotal for each category, a grand "funds
available" total on the report?

Thanking you all in advance,
Pat

Apr 28 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Pat:
You need to have your report grouping by the 'category' field. You can
see how to do this by using the Report Wizard based on your table or
query, and choosing to have it group on the category field. When you
get to the section on Summary Information, click on the Summary Options
button and have it sum the Amount Funded field. Be sure to have it set
to show both Detail and Summary information.

Examine the report and pay special attention to the Category Footer and
Report Footer sections.

HTH,
Jana

Apr 28 '06 #2

P: n/a
pat
Thanks, Jana!

My report works as far as separating the categories and giving me
subtotals. What I need is for the category subtotals to be subtracted
from a number to show available funds. I got the grand total to work by
simply adding an unbound field and putting the code
=Sum(200000-[AmountRequested]). However, the "group" footer has a long
code that seems to be what makes the report brake out according to my
specifications. That code reads as follows: ="Summary for " &
"'GrantType' = " & " " & [GrantType] & " (" & Count(*) & " " &
IIf(Count(*)=1,"detail record","detail records") & ")". How can I
incorporate my =Sum in this?

Thank you so much for responding. I really appreciate it.

Best,
Pat

Apr 28 '06 #3

P: n/a
Pat:

Sorry for the long delay, I was out of town. The unbound field with
the long code is NOT what is breaking your report into groups. That is
just an unbound field that gives you a record count for each group's
details and you can delete it if you don't want that information. What
breaks your report down for you is the group header and footer
sections. You can put an unbound field in the group footer that
calculates your total, and it will subtotal each group for you. Play
around with calculated fields in the various report sections to see the
effect and you will get the hang of it. Try copying your =Sum field
from your report footer into the group footer section to see what I
mean :)

HTH,
Jana

May 4 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.