Calculating a percentage from values in two different fields

Hi,

I have a query called SalesBudget3 that has fields which include: 
CustomerName, Type and then all of the months of the year (January, 
February).  The type field is either Actual or Budget.  My query will return 
two records per client. The first would have their actual sales for 2008 and 
the second would have their budgeted sales. In my report, I have the field 
names in the page header (Type, January etc) and CustomerName in the 
CustomerName header.  The Details sections has their monthly sales figures.  
In the CustomerName footer, I want to calculate percent of Actual to Budget 
per month but it looks like the only thing I can do is to sum them.  Is there 
a way to do a percentage in the form?

Also, I created a separate query called JanPercentage which has the Actual 
and Budget figures for January in the same record.  I have divided the two 
and have a calculated field called JanPer which is actual to budget for each 
client.  This is the result that I would like to appear in CustomerName 
footer. Is there a way through say a subreport that I can insert the results 
needed for each client?  Can someone help?

Thanks,
-- 
Chuck W
0
Utf
1/23/2008 1:49:01 AM
access.reports 4434 articles. 0 followers. Follow

0 Replies
972 Views

Similar Articles

[PageSpeed] 18

Reply:

Similar Artilces:

Convert Numeric field to Date
Hello, I've seen similar questions to this but need some help/clarification. I have Access 2003 with linked tables to a DB2 database. The fields I am querying are numeric in this format 20070810 (yyyymmdd) What I am trying to do is display customer request date and actual ship date in mm/dd/yyyy format on one query. On another query I am trying to summarize item shipments by month. I know next to nothing about this. Any help with baby steps would be greatly appreciated. Probably easiest to convert the numeric value to a string, then convert to a date format for display. You say yo...

How to determine maximum size of field contents
How do I query the maximum size of the contents of a given text field? Also, any suggestions on how to write a query to return the maximum size of the contents of each text field in my database? I'm using Access 2003. Thanks, Pat Len(fieldName) will give you the size of the contents of the field. For any one table you can do the following query. SELECT Max(Len(FieldA)) as ASize , Max(Len(FieldB)) as BSize , Max(Len(FieldC)) as CSize , Max(Len(FieldD)) as DSize FROM YourTable If you want to get the max len of each field of each table, then I would write a VBA procedure to step thro...

Make active contract/contractdetails fields read/write
By default MS CRM makes invoice/Active Contract and it's Contract Lines read-only. Is there a way to make some of Contract Lines/contractdetail fields read-write again ? ...

Count Unique Values In A Filtered Row with Duplicates
Hello, I've tried to copy and paste below the resource spreadsheet I am working on. What I want to be able to do is list out each project and who the project manager is on that project (now some PMs can work on more than one so there are duplicates). Then I want to be able to subtotal the hours (in case I want to review just one project, one city, or one PM) but also remove the duplicates again. Can you help me out please? THANKS, Joe Jan-2006 Feb-2006 Total Hours: 411.00 411.00 Total Resources Total FTE: Total Unallocated: 0.00 0.00 Resource Name Role Regi...

Changing the default value of the revenue field
Hey all. Interesting question... Is there a way to change the default value on the revenue field on the opportunity form from system calculated to user provided. We don't use quotes here, so it is an extra mouse click everytime someone creates an opportunity. TIA, Dave Hi, this field not is mandatory , then you can let it blank and save record if you dont use quotas in this level best regards! "Dave J" wrote: > Hey all. > > Interesting question... > > Is there a way to change the default value on the revenue field on the > opportunity form from syste...

Change Value on Combo Box upon event
Is there a way that I can change the value on a Combo Box to the last record in its record source? I have a combo box that displays Fiscal Years, from 2000 to Current fiscal year 2007. Users can select any year they wish to pull data from based on the year selected. However, I want the combo Box to change its value to the current Fiscal year (which is the last record presented to them when they select the drop down) based on a specific event. Anybody have any ideas? Thanks! heyy try: Me.CourseID = Me.CourseID.ItemData(Me.CourseID.ListCount - 1) cheers jeff "Angel_G" ...

Intercept query parameter values from data refresh
Hi, Is there a way to determine the value a user specifies for a query parameter when using the "Refresh Data" command? I have a External data query to an SQL server database that contains a single parameter. I'd like to determine what the user entered for that parameter from within Excel vba code or other means. I'm running Excel XP on Windows XP. Any ideas would be greatly appreciated. Thanks in advance! Jim ...

SUMPRODUCT using Time between two values
Thanks in advance for any help you can provide me. I have a sheet with the following layout of data (shortened here and changed for obvious reasons) Venue Date Event Start Event End Act Disney 4/13/10 7:00 PM 10:00 PM Goofy MSG 2/13/10 1:00 PM 3:00 PM Basketball Park 3/14/10 6:00 PM 8:00 PM Harlem Globetrotters So, what I have is a user input a Venue, Date, Time Value, and I would need the Act returned... For example, Disney, 4/13/10, 8:25:13 PM, would ou...

Outlook Coding
I have code to send emails progamtically from a module in Excel 2000 using OUtlook 2000, but now I need to have the code select 1 of 5 different email addresses and potentially a different mail server. Any ideas? --------------------------------- Set NewMail = CreateObject("Outlook.Application") Set objmail = NewMail.CreateItem(olMailItem) With objmail .To = Email .Subject = "Quotes for " & Name .Body = emailtext .Send End With Set NewMail = Nothing Set objmail = Nothing ---------------------------------- Submitted...

How do I link data from two different excell files?
I have two excell spreadsheets that need to be matched up and merge a certain field. Is there a statement that wou vlookup HTH, -- Gary Brown gary.DeleteThis2SendMeAnEmail.Brown@kinneson.com "twinclstar" wrote: > I have two excell spreadsheets that need to be matched up and merge a certain > field. Is there a statement that wou ...

Migrate CRM 4.0 instance to another server on a different domain
Hello, What would be the best way to migrate our current CRM database to another server on a different domain but still within the same forest? We initially had intended on implementing our CRM at our off-site datacenter but due to some DNS/Network issues we weren't able to add users across different domains. Our deadline was fast approaching so we setup a server locally. Now that we have the issue resolved at our datacenter, we are ready to move the local database to the datacenter. What is involved with this process and what would be the best way to do this? Thank you very much...

How do I find days between two dates?
I'm using this formula =TODAY()-(7/1/2004) to find the days between today (4/2) and 7/1. The result is 38079.99651. Why doesn't this give the days? Also, is there a way to display the number of months and days in the same cell? For example =MONTH(TODAY())-MONTH(7/1/2004) with the day formula and have the result read "2 months 28 days" or so? Thanks, Brett Try: =today()-date(2004,1,7) date() is y,m,d--did you mean July 1, 2004 or January 7, 2004? And format the cell as General. 7/1/2004 looks like arithmetic (7 divided by 1 divided by 2004). And when you format a da...

Two Questions.
I run Money 2003 on XP with all current updates. The first one has probably been talked about frequently but after reviewing the current threads I only saw issues with Money 2002. But here goes: why can't I just open my money file without using Passport sign-in? I have clicked the check box saying that I don't need to use the internet and still get no where. This is just an annoying issue but I would like to not have to use passport at times. The second issue is a little different. I created a new investment and after I was finished, I noticed I used the incorrect name with the &q...

Calculating an average when total is 24 hour
I'm trying to calculating an averge handle time using total number of processed emails divided by the total handle time. However, since the time could be longer than 24 hours, I'm using the following expression =Int(CSng(Sum([Handle Time])*24)) & ":" & Int(CSng(Sum([Handle Time])*1440))-Int(CSng(Sum([Handle Time])*24))*60 & ":" & Int(CSng(Sum([Handle Time])*86400))-Int(CSng(Sum([Handle Time])*1440))*60 This is causing the average field to have an error. How can I correct that? Additionally, I would like the total handle time to be in an hh:nn ...

query destination field
How do you change a destination field of a query in Excel? I have the data from the query being sent to cell A4 but need it to go to cell A1. I tried the "edit query" option but with no luck albert I think you can just delete Rows 1:3 and it will adjust accordingly. -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com "albert" <anonymous@discussions.microsoft.com> wrote in message news:14E1E67B-F7A0-4FD3-A78E-C4E1CCC22453@microsoft.com... > How do you change a destination field of a query in Excel? I have the data from the query b...

how do I omit a duplicate field in a merged document?
I want to merge a list from Excel to a Mail Merge in Word. It works, but there are fields that are duplicates in Excel that I do not want in Word. For example, Mr. Smith has two lines of information in Excel which generages two letters in Word. Is there a way to eliminate this? You could add a column in which you count the occurrences of each entry. For example, if you have a customer number in column A, enter the following formula in row 2, and copy down to the last row of data: =COUNTIF($A$1:A2,A2) When merging in Word, filter this column for rows with a 1. dawnlin wrote: > ...

clear check mark when field changes
I have a field (Current CommTech Level) that changes from CommTech 2 to CommTech 3 and so on. I also have 2 fields with checks marks in them, Passed Written Exam and Passed Practical Exam. When the CommTech Level changes I need the check marks to clear. We already have record of the testing process off site, so keeping track of the testing process would be redundant over the redunancy that is already in place. Thank you for your help -- Scot Rawlings Technical Trainer Comcast Auburn, WA Message posted via http://www.accessmonster.com Hi Scot, Use the AfterUpdate event of the control...

Add Class from Typelib Wizard
My project uses a custom COM component that another vendor created for us. I can successfully "add class from typelib" with VS2003. When I try to do the same thing in VS2005, I can see and select the typelib, but no interfaces will show up in the "Interfaces" box. This prevents me from wrapping the COM object and using it from my VS2005 MFC project. Does anyone know what might be happening here? The COM component is correctly installed on the computer and works well. http://msdn2.microsoft.com/en-us/library/5k45w2wh(VS.80).aspx Ed D wrote: > My project uses a cust...

Replace text in field code
I have some REF fields that point to bookmarks for document ranges that are deleted in my VBA code. I want the REF field to point to a new bookmark. I want to use something like the following myRange.fields("REF OQTempRHMappingSection \r \h").code = replace(myRange.fields("REF OQTempRHMappingSection \r \h").code, "TempRH", "TempOnly") Of course, this does not work or I wouldn't be writing this question. Is there a way to accomplish what I'm trying to do? If I reveal codes in the document, I can perform the edit manually. I think...

Auto line wrap within a field.
I am working with a document wherein there are 5 fields with two being lengthy text fields. I wish to make it fit on one page horizontally because as it is it causes the font size to be very small. I would like to make these fields more narrow and have the text automatically line wrap rather than be chopped off. Is this possible or will all the existing fields need to be edited one by one? Thanks, gtb Select the entire column you wish to adjust. You will find the "wrap text" checkbox in the format cells > alignment dialog box. Once that box is checked, text you enter int...

value of y axis position
Hi, all, After reversal of x axis, I moved y axis from right to left. However, value of the y axis appears inside of the chart, how can I move them outside. Many thanks. Billy Your statement is a little vague. If in Format - Axis, I use "Values in reverse order," it moves the Y axis to the right side, but the Y labels appear outside the chart, not inside. Or I can use "Value (Y) axis crosses at maximum value, " which also moves the Y axis to the right side, but the axis labels are still on the outside. Try this. With the Y axis selected (click any y axis label)...

after adding data, grafics not calculated until save and reload
I have a speadsheet ~52MB used for 42MB without problems in which I select y a list and with Vlookup analyse a subset to produce grafics of the data I have added some data ( of last year ) on one sheet of that now the grafic sheets wil not be recalculated until I save and reload the whole sheet I tried already: coppy the project coppy all sheets to a new project make a new grafic ( out of 30 grafic) reset fonts on the grafic sheets renewed title e.g. delete the added data of course I can start all over but that meens a 14 day job and no guaranty that is will help does some one know how ...

Why do OnLButtonDown and OnMouseWheel use different coordinates?
I raised a WM_LBUTTONDOWN AND WM_MOUSEWHEEL at the same position of a window, but found that the CPoint pt in the OnMouseWheel is different from the CPoint point. I tried many times, the CPoint point is alway the same at a certain position but the CPoint pt varies in a range of 100, why are these two coordinates different? And how can I transform them? "wang" <wang_jia_ji@21cn.com> wrote in message news:087701c38559$d97828a0$a301280a@phx.gbl... > I raised a WM_LBUTTONDOWN AND WM_MOUSEWHEEL at the same > position of a window, but found that the CPoint pt in the >...

IF, AND for different Columns
I have a worksheet for a meeting that I am trying to get to where I can use the formula's and it will calculate as soon as the data is pasted from another report. I need the following: Column E will have the following options: BUY STK BUY/STK STK/BUY FRT Column F will have the following options: CAWPHY CAWPB CFWPHY CFWPB FRT (plus way more to list) The total of each Row is listed in Column J So, if Column E has "BUY" and Column F has "CAWPHY", I want the sum which is in Column J to be in cell E39. If Column E has "STK" an...

Wildcard with two or more criteria: pls help
_*Number*_ _*Status*_ _*Date*_ N01-02 Draft 04-May-05 N01-02 Draft 04-May-05 N01-92 In Process 15-Oct-05 N01-88 Draft 15-May-05 N01-29 In Process 09-Jul-05 N01-32 In Process 09-Jul-05 N02-22 Draft 24-Aug-05 N02-27 Draft 24-Aug-05 N20-12 Assigned 03-Dec-05 N24-19 Draft 24-Aug-05 N33-13 Doc Written 04-May-07 N44-04 Cancelled 15-Oct-05 My header are Number, Status, and Date (columns A,B and C). The dcuments represent my rows (A2-A13). In my real file, I have over 300 documents. The docum...