473,544 Members | 2,340 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Report "Percentage claculation of Totals and Grand Totals"

3 New Member
Greetings Access Group,

Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.

I currently have a report that contains 2 totals and the percentage of the difference of the 2 totals. I will refer to the totals as: total "A and "B", and the percent calculation I will refer to "C".
The Report is based off a query and results in multiple records being viewed on the Report. The "Detail" section of the Report returns the actual amounts for "A" and "B". "C" is the calculated percentage difference B & A: (Query field) Expr1 SUM([b]/[A]). The report DETAIL percentage returns the correct percentage, the problem is:
The "Footer" section of the report and the "Report Footer" are not returning the correct percentage. I need the reports "Footer" section to return the % difference of the "Sum" section of the "Footer" and "Report Footer". My logic is such: (Footer), "C" equals: the sum of all "B" records - divided by - the sum of all "A" records. I also need to return the same result in the "Report Footer" "Grand Totals”, my logic being, (Report Footer), "C" equals: the Grand Total for all "B" records - divided by - the Grand Total for all "A" records.
I have exhausted my efforts and realize that I am clearly moving in the wrong direction. Your help would be greatly appreciated, and please remember my knowledge of Access is limited.
Thank you for assistance
Aug 17 '07 #1
4 5644
MGrowneyARSI
90 New Member
I’ve done something similar in the past you could try setting the field C on your report equal to a Dlookup of A / Dlookup of B you can do this in the properties box or the on open event for the report don’t know if this will help but that is the way I would do it
DLookup("[FIELD]","TABLE/QUERY","CRITERI A")
Me.CompanyID = DLookup("[companyID]", "Company", "[company]=" & [Forms]![Login]![Company])
Me.CompanyID = the field you are setting
("[companyID]"= the data you are feeding to the field
"Company",= the table/query you are looking in
"[company]=" & [Forms]![Login]![Company]) = in this example you are finding the record were company in the table/query = company on the login form you do not need to use criteria if there is only one record at a time and there are other ways to do dlookups

If you knew all of this already then just do something like this
C = DLookup("[FIELD]","TABLE/QUERY","CRITERI A") / DLookup("[FIELD]","TABLE/QUERY","CRITERI A")
Aug 17 '07 #2
mlcampeau
296 Recognized Expert Contributor
Greetings Access Group,

Being relatively new to Access, I try to work through problems on my own and have been very successful, although I have a conundrum that I have been working on for two days (total 10 hours) and I have yet to resolve it. please excuse me for my lack of terminology, I will try to provide you with the best of information.

I currently have a report that contains 2 totals and the percentage of the difference of the 2 totals. I will refer to the totals as: total "A and "B", and the percent calculation I will refer to "C".
The Report is based off a query and results in multiple records being viewed on the Report. The "Detail" section of the Report returns the actual amounts for "A" and "B". "C" is the calculated percentage difference B & A: (Query field) Expr1 SUM([b]/[A]). The report DETAIL percentage returns the correct percentage, the problem is:
The "Footer" section of the report and the "Report Footer" are not returning the correct percentage. I need the reports "Footer" section to return the % difference of the "Sum" section of the "Footer" and "Report Footer". My logic is such: (Footer), "C" equals: the sum of all "B" records - divided by - the sum of all "A" records. I also need to return the same result in the "Report Footer" "Grand Totals”, my logic being, (Report Footer), "C" equals: the Grand Total for all "B" records - divided by - the Grand Total for all "A" records.
I have exhausted my efforts and realize that I am clearly moving in the wrong direction. Your help would be greatly appreciated, and please remember my knowledge of Access is limited.
Thank you for assistance
You could duplicate your "B" records and "A" records textbox in your details section. In the properties, change Visible to No and Running Sum to Over Group. Name each of these textboxes appropriately. In your footer, create a textbox for "C" with the expression: =[NotVisibleBText boxName]/[NotVisibleAText boxName]. Follow this same logic for your Report footer.
Aug 17 '07 #3
Micheal
3 New Member
I could not get the DLookup to work correctly, so I kept Reviewing formula's then noticed that I my calculation was; =Sum([b])/([A]), which was incorrect, it should have been: =Sum([b])/Sum([A]). Which means I kind of misdirected you in my first question. Thanks for your help.
Michael
Aug 17 '07 #4
MGrowneyARSI
90 New Member
Well that will work LOL but I would keep the Dlookup in your bag of tricks they can be a pain but they're very usfull as well good luck
Aug 17 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
2716
by: ahaque38 | last post by:
Hello. Using A2K SP3, I am having the following problem with a report using "Sorting and Grouping". I have recently added a grouping in the reports for "Category2<>'CONTRACTS'". I have reports at the plan (overall totals), department and division levels which have sorting and grouping implemented with this new
3
1973
by: Rabun | last post by:
Heres one that is giving me fits ( = = Access newbie), more than likely something simple that I blew right over . . . any help is appreciated - I have a report based on a query, with several columns I need to work with. Values in the cols are " Over " and " Under", as text values. The query is for a start / end date, my count of records...
4
1451
by: MLH | last post by:
I get something like this a percentage of the time when I open an A97 report by dbl-clicking it in the database window. I can not remember seeing tnis when the report is opened in code, I can not remember it happening when the report is opened first into design view and then into report view. But I can remember it happening when first...
8
14251
by: Don Wash | last post by:
Hi There! I'm using VB.NET to create a TreeView application and unfortunately I could not find "Key" property in Node items of the TreeView. We used to have "Key" property in TreeView node object in VB6. What is the equivalent of "Key" property of TreeView node in VB.NET? Has the property name "Key" has been changed to something else?...
0
1758
by: colt | last post by:
I have a query that is pretty straightforward and I am trying to generate 5 reports from that query. The reports produce totals by Project Manager, Category, Client, Account Manager and Account Executive. The first three reports work fine. However, when I try to change the sorting and grouping to either Account Manger or Account Executive,...
97
5443
by: Master Programmer | last post by:
An friend insider told me that VB is to be killled off within 18 months. I guess this makes sence now that C# is here. I believe it and am actualy surprised they ever even included it in VS 2003 in the first place. Anyone else heard about this development? The Master
5
4233
by: Knuxus | last post by:
Hello. There is a problem in one of my forms, which also applies to reports in the same way... there is 3 fields Price and Quantity and Total Price. The field Total Price is made in expression builder, and is set to be a Currency(actually Euro) format. =* It calculates the total price... but in Totals i cannot acess the function Sum. only...
0
2092
by: Josetta | last post by:
Here is something really bizarre that is happening...let me see if I can describe it. I have a report which has two locations on it. Primary Location and Secondary Location. I have both locations grouped. For example: Primary Location (group header 1) is a city. Secondary Location (group header 2) is a Street Address.
5
2406
by: buddyr | last post by:
Hello I am working on time sheet form. Whether I create fields in query = \60 & format( mod 60, "\00") or Round((Table2.OUT-Table2.)*24,4) AS HOURS When I try to add my totals in the fields to give me one Total- It only lets me if I have every field entered data into- And so If somebod is sick and does not fill in Tuesday IN and OUT The...
0
7437
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...
0
7373
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7781
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...
0
7717
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...
1
5306
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...
0
4930
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...
0
3421
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1848
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
0
677
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.