Modules & VBA :: Calculate Percentage Of Total Proposal Amount
Dec 18, 2014
I have two numbers, i need to calculate how much percentage of one is the other. E.g
num_1 = 100
num_2 = 10
percent = 10%
Users enter a dollar amount (retainer) and my code should calculate the percent of the total proposal amount.
Actual Code
Private Sub cmdSubmit_Click()
Dim intProposalTotal As Double 'if i use interger i get "overflow" error
intProposalTotal = Nz(DLookup("ProposalTotal", "qryPropsalTotalForRetainer", "proposal_id=" & Me.proposal_id), 0)
[Code] .....
billing_retainer_percent is formatted to Percent with 0 decimals. In the actual table Type - Number, Format - Percent, Field Size - Single. What i get is 100% instead of 10%.
I am trying to count how many of the "same" and "differences", as well as calculate the percentages of the number of "same" over the total amount. To clarify, I work at a nursing home, and I need to calculate the number of people who were admitted to our facility and then to the hospital for the same diagnosis, and a different diagnosis. Then, out of the total number of people who were admitted to the hospital from our facility, I need to calculate how many of those people had the same diagnosis or a different diagnosis.
Also, I need to categorize these diagnosis by each type of diagnosis.
I have a form that have textbox that calculate total and then i want to convert numbers to words. Iv got the code with function currencytotext but when i set the code source of another textbox to:
=currencytotext([inv_total])
I have an error. I saw this on internet but i think bcz I am using access 2013 may be the syntax is different a bit.
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
I have One table(and designed form from it) in which i have recording the daily transactions(it is like a daily book). Daily transactions took place like Purchase of items of Amount 45, sale=70, sold on account=100 etc.
What problem i am facing is: I would like to Add Cash In Hand amount and would like to show it on my form. When day end I would like to move remaining cash In Hand on separate column of that date. The next day i would like to take the previous Remaining Cash in Hand as Cash In Hand at Start for next date and so on.
In a report, I need to calculate the percentage of clients that respond "Yes" to a question on a survey. I need to create a query that will return the count of the number of "Yes" responses, and the number of total responses. For example, if 10 clients complete the survey, and seven respond "Yes", I need the 2 fields in the query to be 7 and 10. So far, I have only been able to do this using multiple queries. Thank you.
I need to create a derived attributes (total hours left, total hours used).
In order for the calculation I have days_allocated in the employee table (this is converted into hours by multiplying this by 24).
How can I create a field in a query that works out the amount of hours used and left?
Here is a typical holiday record (screenshot of my query that I am trying to do)
http://img3.freeimagehosting.net/uploads/th.8af9f3cd25.jpg (http://img3.freeimagehosting.net/image.php?8af9f3cd25.jpg) Can someone please help me create a formula.
"This query should prompt the user to enter two dates and than calculate the total amount paid for all invoices in the Amount field between those two dates (inclusive)."
Query is based on table Invoices.
I have created the query and dates part works fine. However,when I run Sum for the Amount field, instead of total amount query displays amount of each invoice between those dates. How to get just Total (amount of each invoice added up)?
We have a form where we enter various amounts and a box where these are added together to calculate taxes. This amount is shown on the screen but we need to save this total amount in the table where it can be used in other calculations in other forms
The control in the box looks like this:
=[registration fee]+[membership fee]+[fee per term]+[additional classes fee]+[costume]+[books]-[discount]+[applicable gst]+[uniform fee]
How can I get the result of this formula saved in another table field?
I am making a query that calculates how much costs i have per job. I'd like to create a function in the query that can calculate how big a percentage each job is. (need total for every job/ total for all jobs) but so far i haven't been succesful in this.
Hi, Thanks in advance. I am trying to calculate percentage, it has to round to the nearest, and also if the percentage is greater than 100 then it has to write 100%. formulae: c=(a/b)*100, example: c=(8/3)*100 =266.66% but this is more than 100% so i need this as 100% example2: c=(2/3)8100=66.66% but i need it to round as 67%
i've 3 controls on a form a,b,c. so c has to calculate by itself when they enter data on a and b. thanks, sam
Can anyone suggest a method for doing what the title asks. I basically have a single table with several fields. One of the fields is the length of music tracks in seconds. What i want to do is to set criteria so that when a query is run the records to not add up to more than 900 seconds. 1stly) Is this "do-able" using queries or do i need to start implementing sql statements which i have limited experience of? 2ndly) Can anyone recommend a suitable method to do so provided it isn't very complex.
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh] data eg (this is how I would like it to be inputted into the table) 7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
Hi, All: I have been struggling for this question for a long time.
I have a total query coming from two tables. This query has following field: productline device component
The table has more field. One of them is status for component field.
My application is that there are many productline. Under each productline, there are many devices. Under each device, there are many components. Each component has one of 4 statuses. The status is text value, 'Yes', 'No', 'UR', and blank.
In the form, I need to use continuous form to display each device with totalcomponent (I use Count of component), percentage of status1 based on totalcomponent, percentage of status2, etc.
My question is: I tried to use Count(IIF([status] = 'Yes', 1, 0)) to get percentage. But [status] = 'Yes' seems not right because I got a count of all statuses, the same result of CountOfComponent
I have an Access 2009 report that displays the sums at the bottom of some columns. Is there a way to calculate and display the percentage one sum is of another?
To calculate the percentage of the number of passengers in the total number of passengers per single order using query:
Code: SELECT
Code: A.Date, A.Plate, A.Pax, A.Agency, A.Code, IIf ([pax]> 0, Round ([Pax] / (SELECT Sum (Pax) FROM Sheet2 as B WHERE B.Date = A.Date and B.code = A.code), 2), 1) AS KOR FROM Sheet2 AS A ORDER BY A.Date;
Query works fine in all cases except one, and that is when the encounter a same order in one day
01/04/2013. D_1 0pax 01/04/2013. D_1 5pax
in this case the formula gives a coefficient of 1 to 0 passengers and coefficient of 1 to 5 passengers, should be given a score of 0 0 of passengers and 1 to 5 passengers
I am trying to get the main form to display a total Amount for all records in Purchase order details but the control displays Error:
I have a Form called frmPurchaseOrder with a sub-form sbfrmPurchaseOrderDetails
sbfrmPurchaseOrderDetails takes item details from a table based on what is selected in the combo box then fills out four additional fields in the same sub-form.
SELECT tblItemListDetails.ItemID, tblItemListDetails.ItemName, tblItemListDetails.ItemSize, tblItemListDetails.ItemUnit, tblItemListDetails.ItemUnitCost, tblItemListDetails.ItemBrand FROM tblItemListDetails WHERE (((tblItemListDetails.Supplier)=[Forms]![frmPurchaseOrders]![SupplierCombo]));
Private Sub cmbItemName_Change() Me.txtSize.Value = Me.cmbItemName.Column(2)
I have a subform in columns that has a list of invoices bound together by the site number on the form there is a Expense code that runs 51 to 95.
On another tab I want to add all the invoices for a expense code together and display the total amount
Below is an example of invoices
IDSite CodeInvoice DatePOInvoiceContractorInvoice AmountExpense Code 10289S20/09/2013 346603410Dj Commercial Cleaning Ltd 25.2362A 20289S28/09/2013 346061141Platinum Landscapes 240.0055A 30289S03/09/2013 353112021Clean And Green 167.0063A 40289S02/08/2013 353112015Clean And Green 174.0063A 50289S19/08/2013 111115271Southern Electric 189.5679A 60289S19/08/2013 346061121Platinum Landscapes 240.0055A 70289S02/06/2013 353112009Clean And Green 160.0063A 80289S02/05/2013 353112003Clean And Green 181.0063A
What I'm looking to do is calculate the success rate (%) of compliance rates with Quality Control paperwork. Essentially, each record has 12 "check box" fields representing the different QC sheets that are submitted each day.
I'm very new to Access and I'm attempting to write an expression in a query that will calculate the % of the count of "Exchange" field (Exchange is a text field and is grouped and the count based on each unique name) where the total count is based on the filter where "Group" = 'FS' or 'S'.
The below seems to work, but there is a better way of going about this (especially if I have to add more filter criteria). I added a pic of the query I'm trying to build.
PercentofTotal: (Count([Exchange])/DCount(Count([Exchange]),"[Holdings]","[Asset Group] = 'FS' OR [Asset Group]='S'"))
