473,836 Members | 1,563 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Calculation in a report

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
3 2154
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
3917
by: Del | last post by:
Thanks in advance for any help. I have a database that was created in Access 2000. Several users have been upgraded to Access 2003. Since upgrading to 2003 we have noticed that some of the calculated fields are not being populated. The database is a samll invoicing database on the form and report we have columns call Unit Cost. This is the actual cost of the part and is pulled from our parts master table and is displayed in a sub form...
2
2474
by: DebbieG | last post by:
I have no idea how to start with this one. I have a subform where records could look similar to: Infraction Date Points 01/01/2000 3 06/01/2002 1 Somehow, I've got to calculate the points the driver has as of the current date. For instance, in the above example:
6
5305
by: jimfortune | last post by:
In: http://groups.google.com/group/comp.databases.ms-access/msg/60d7faa790c65db1 james.ea...@gmail.com said: :Ok, I've answered the first half of my own question. From a MSDN :article, I can now use the following to reference the last primary :key's values: :
4
3766
by: vg-mail | last post by:
Hello all, I have identical design for form and report but I am getting calculation error on form and everything is OK on report. The form and report are build up on SQL statement. The calculation is very simple. The calculation is done in an underling query if I can call it a query or I should call it a SQL statement. It looks like a query but it is not saved as the query. The calculation in that query is very simple - ExtendedPrice:*....
1
2239
by: sgmarty | last post by:
Hi, I have an interesting problem. Without using reduncant data, how can I design a calculation field to get a running total from values in different records? I'm designing a usage/month report, and I'm confused. Date Value Calculation Record1: 08/05/06 12345 Record2: 09/05/06 23456 (23456-12345) Record3: 10/05/06 34567 (34567-23456) Thanks for the help. Steve
5
2384
by: JRNewKid | last post by:
I have an Access report and in the body of the report there is a calculation: =DateDiff("n",,)/60 This is in a field called dlyElapsedTime. at the end of the report I want to have a total of this time, so can I put a sum in the footer area? I tried it, like this: =Sum() and I get the label but not the total, it just says #Error. Thank you Also THANK YOU Corster, I learned a lot from your post.
3
1952
by: ncsuwolfe | last post by:
Greetings, I am creating a frequency report. I have 41 records, each record has sub-records from a source table. I am trying to detemine the frequency that each sub-record appears in a main record. So, if a sub-record appears 5 times, the formula would be 5/41=0.12. The frequency calculation I currently have is residing in the footer of a report listed by sub-record. The footer displays sub-record name, total observations, and the...
11
2251
by: CrostonScottish | last post by:
I am having a few difficulties calculating values in my report with the sql code that i am using. It is made up of two parts: 1: (This bit is ok) A text box has the control source set as : *10)+(*9))]. This returns the value i expect when i test it. The problem is: 2: When i set the control source of another text box to take the value from text box 40 above and perform the calculation below it returns a spurious value: /(+)]
1
1575
by: JCMcAbee | last post by:
Hello. Access 2003. I hope this makes sense. I request assistance with the following calculation problem: In a grouped(?) footer of the report I have the following calculations in text boxes, and they are working well: Column1: =Sum() Column2: =Sum() Column3: =Sum() Column4: =(Count()+Count()+Count()-Count())/4 Column5: =Sum()+Sum()+Sum()+(Count()+Count()+Count()-Count())/4
3
1854
by: Amit | last post by:
Hello Gurus, I am very new to .net . My application is calculating values on run time ,and after all the calculation , i want to print all calculation as a report to show it to user. please advise how to achieve this . this is very urgent .
0
9826
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10860
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10560
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10261
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7796
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6984
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5831
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4466
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4026
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.