Percentage Calculation In Total Query
Oct 11, 2005
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
Really need help
Thank you much
awu10
View Replies
ADVERTISEMENT
Aug 5, 2014
I am trying to set up a calculation between two values to show the percentage difference. In Excel, for example, I would have two values, £905,175 and £891,563, and I would enter =A1-G2)/ABS(A1), which would then return a plus or minus percentage value. how to do this in a query using Access 2010?
View 3 Replies
View Related
Apr 14, 2015
I've just made this query at work, it brings up the results from a database of telephone surveys. about going all gestapo on the table names and codes, I'm unsure as to how told off I could get for placing identifiable images on the internet
query.png
basically I would like to extend this query so it shows the sum of the "CountOfQ1 22" column and also shows what percentage of that total each entry in the "Q1 22" column is.
This will enable me to have results for the day sent to me at home every night at close of business as the person supervising the call centre at the time can run the query and email me the results.
View 4 Replies
View Related
Jul 17, 2005
In a query I am calculating a percentage from two fields in a table. ever, if the values are 0 and 0 then the percentage comes up as an error (#Error). How do I use the nz function in this case to convert error into zero and where in the query do I put the expression?
View 1 Replies
View Related
Feb 26, 2007
Hi guys
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.
Anyone who could help me?
Cheers,
Takstein
View 3 Replies
View Related
Oct 15, 2013
I have a query that calculates a percentage that works fine. The sql is like so:
SELECT Table1.cost, Table1.sales, Table1.location, [sales]-[cost] AS GrossProfit, [GrossProfit]/[sales] AS GrossProfitPC
FROM Table1;
If I try to set criteria to select records based on the calculated value of the expression (eg; only select records with a GrossProfit percentage > 50) when I run the query it asks for a parameter as if the field [GrossProfit ] were a parameter.
Is there a way i can set a criteria based on the value of the percentage field?
View 2 Replies
View Related
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%.
View 4 Replies
View Related
Aug 12, 2013
I have built a query that lists all the members in a group aged 15 to 18. Now I need to build another query that counts a subsection of that group (the number who gained employment in the quarter) and express this as a percentage of the whole of the 15 - 18 group.
I presume I can use the original query to get the number of 15 - 18 year olds. I can write the query to calculate the subset of 15 - 18 year olds who gained employment, but how do I get the total numbers of the groups and express them as a percentage?
View 1 Replies
View Related
Apr 19, 2013
I have a question about summing information on a report. I am developing a report to see have percentage of time Staff work within a certain area. On the data base the time percentages entered as ranges e.g. 75-100%, or 11-25%. We needed to figure out the total percentage of time so I created a report to add the total time. I used a text box which it titled FTE_Total if embedded iif functions to display the total time. Here is the function I used:
=IIf([FTE_Percentage]="76-100%",1,IIf([FTE_Percentage]="51-75%",0.75,IIf([FTE_Percentage]
="26-50%",0.5,IIf([FTE_Percentage]="11-25%",0.25,IIf([FTE_Percentage]="1-10%",0.1,0))))).
This worked perfectly however I cannot get the FTE_Total fields to sum. I tried the following function in the group footer: =Sum([FTE_Total]) but everytime I try to look at the report in report view I get a message asking for the FTE_Total Parameter.
View 1 Replies
View Related
Jul 9, 2013
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.
View 14 Replies
View Related
Nov 20, 2006
Good Day!
I seem to be running into a calculation problem in one of my queries. I have two fields in the query, AdmitDate and DischargeDate. I built in an expression, Exp100 to catculate the date diff in days between these two dates. Now what I want to do is display a count of the grand total days. So, if I have the following three records:
ID AdmitDate DischargeDate Exp100
1..... 01/01/06.........01/15/06............15
2......07/12/06.........07/19/06............7
3......04/27/06.........04/30/06............3
My query should display:
Healthy Plan Total Patients=3
Healthy Plan Total # Days = 25
How can I program this to correctly display this information? Thanks for any help or advice, it would be most welcome at this point. Cheers.
View 10 Replies
View Related
Aug 10, 2014
Is this possible? In my invoice form I have 3 different tax rates. I have put a calculation in each of the Control Sources to calculate the invoice sub total:
=[Invoice Sub Total]*0.13 =[Invoice Sub Total]*0.05 =[Invoice Sub Total]*0.09975
I don't always use all the tax rates. I would like to be able to go back into a tax rate field I don't need and delete it.
View 11 Replies
View Related
Nov 20, 2007
I have an Access query that lists completed company forms by days to complete in descending order. I want to use the reccord number and total record count to present the percentage of forms that were completed at each completion date. Ther purpose is to quickly show that 80 percent of forms are completed within 60 days. This is easy to do in Excel using the Row() and Count() funtions such as 1-Row()/Count($A$1:$A$5000).
Is there a way to do this same thing in Access?
View 6 Replies
View Related
Feb 21, 2013
I have created a table with four fields. The names are:1stBusOn, 1stBusOff, 2ndBusOn, 2ndBusOff.
I want the time for the first two fields and the time for the second two fields to end up in a field called TotalRideTime.
Also, if only one set of the fields are completed I still want that total time to in up in the the TotalRideTime field.
View 1 Replies
View Related
Mar 16, 2013
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?
View 4 Replies
View Related
Nov 14, 2014
I need a solution for the following problem:
For example:
............F1 F2 F3
Rec1 ....4 ..2 ..5
Rec2 ....3 ..3 ..1
Rec3 ....1 ..2 ..1
Now, I want to know how many 1s are there in Rec3 the answer is obviously 2 but I want a field(F4, for e.g) that calculates F1+F2+F3 showing all their 1s 2s and 3s.
A solution I found was...... F4=IIf([F1],1,0)+IIf([F2],1,0)+IIf([F3],1,0)
But I have over 50 Fields that contain 1s and 2s and I can't count them all by using this formula as it will be tremendously long and access will reject it.
So I need another formula(or function) that will look up for the 1s across multiple fields and show the total count for each record in a new field.
Tried 'look up' function but achieved nothing ....
View 3 Replies
View Related
Jul 23, 2015
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..
View 9 Replies
View Related
Oct 28, 2005
Hi,
I am creating a database to help me monitor the students in my class. I have a query that takes a student mark from one table and compares it with the maximum mark available for the assessment/homework. In a calculated field called Percent I divide the mark by the maximum mark and then multiply by 100. This gives me the percent. I then have another calculated field which awards a grade from A to C and if they don't achieve a C they are awarded a Fail. The IIf statement looks like this:
Grade: IIf([percent]>="70","A",IIf([percent]>="60","B",IIf([percent]>="50","C","Fail")))
Here is the bit I don't understand. If a student gets 99 % they are awarded an A (cut offs are: 70% and above for an A, 60% to 69 % for a B and 50% to 59% for a C. 49% and below gets a fail). If they get 65% they get a B... all appears fine. But, if they should happen to get 100% the query says "Fail". Can someone shed any light?
Regards
Kevin
View 1 Replies
View Related
May 11, 2005
I created a "Percentage" column and wish to update it by using the "Loan Amount". I did an update query and in the update field i keyed
=[loan amount]/sum(loan[loan amount])
but it doesn't show up the records, anyone could help me pls? thanks
View 2 Replies
View Related
Jan 29, 2007
Below is the code I am using in a query that produces results that look like:
Fault Category------------No Fault Totals
No Faults-----------------------77
Total Work Units---------------521
I would like to also show in the query the percentage of No Faults. In this instance I would need to divide
the 77 no faults by the 521 total work units. Is there a way to do this and show the answer in the query.
I do not want this in a report, just the query.
SELECT FaultCategory, COUNT(*) As [No Fault Totals]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
FaultCategory IN ('No Faults') AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]
GROUP BY FaultCategory
UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals]
FROM (Select Distinct [WorkUnit]
FROM WorkUnitsFaultsMainTBL
WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND
[TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt]
AND
[Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;
View 1 Replies
View Related
Jan 29, 2007
Below is the code I am using in a query that produces results that look like:
Fault Category-------No Fault Totals
No Faults-------------------77
Total Work Units-----------521
I would like to also show in the query the percentage of No Faults. In this instance I would need to divide
the 77 no faults by the 521 total work units. Is there a way to do this and show the answer in the query.
I do not want this in a report, just the query.
Code:SELECT FaultCategory, COUNT(*) As [No Fault Totals] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND FaultCategory IN ('No Faults') AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt] GROUP BY FaultCategory UNION ALL SELECT 'Total Work Units' As FaultCategory, COUNT([WorkUnit]) As [WU Totals] FROM (Select Distinct [WorkUnit] FROM WorkUnitsFaultsMainTBL WHERE BuildID IN ("E010","C809","F001","C810","F187","A910","M173","M174") AND [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND [Forms]![Queries_ReportsFRM]![EndDateTxt]) As vTbl;
View 1 Replies
View Related
Apr 16, 2013
How to calculate the percentage of kilometers per order compared to the total number of kilometers per carrier.
In the attached screenshot send queries where I need formula.
Mileage on the orders of the field called "Korkma" . The formula should look like: # [Korkm] / [total kilometers per carrier] #.
I do not know how to get the total mileage driven by the carrier (the carrier is in the query in the Field Name Plate) for the month.
For example:
date 1.1.2013.,Plate:Pribus Korkm= 37
date 2.1.2013.,Plate: Pribus,Korkm=56
date 10.1.2013.,Plate: Pribus, Korkm= 70
Formula: [37]/[163] = 0,23
[56]/[163]= 0,34
[70]/[163]= 0,43
View 1 Replies
View Related
Apr 25, 2014
I have a database which tracks the performance of my team and how long it took them to send something out to the customer. We have a target of 5 minutes.
So I quite a few queries to drill down this information. I have a summary query that takes that information and tells me the total amount of things sent, the amount of things sent in time and the amount of things sent late.
My team have to get at least 95% out on time. So how would I go about adding this bit into the query. The calculation is:
The amount sent on time / the total amount * 100
But is there anyway to add this into a 4th column displayed next to these figures?
Looking around a few people have talked about SQL but I no nothing about this and it seems quite daunting, is there a way to do this as a calculated field?
View 1 Replies
View Related
Apr 12, 2013
I have created a cross tab query that contains a row heading for Entity and Total Cases. I would like to have a percentage of the Case Total for Each Category as well as the count for each category. It works fine for just the counts and here is the sql behind it:
TRANSFORM Count(CASEDATA.CASESTATUS) AS CountOfCASESTATUS
SELECT IUIDCODES.CODEDESCRIPTION, Count(CASEDATA.CASETYPECODE) AS CountOfCASETYPECODE
FROM (CASEDATA INNER JOIN CASESTATUSCODES ON CASEDATA.CASESTATUS = CASESTATUSCODES.CODEID) INNER JOIN IUIDCODES ON CASEDATA.IUID = IUIDCODES.CODEID
WHERE (((CASEDATA.CASETYPECODE)=63) AND ((CASEDATA.FYSTATUS)=6 Or (CASEDATA.FYSTATUS)=7 Or (CASEDATA.FYSTATUS)=8))
GROUP BY IUIDCODES.CODEDESCRIPTION
PIVOT CASESTATUSCODES.CODEDESCRIPTION;
View 2 Replies
View Related
Mar 28, 2015
I need to do a update query with prompt to input a percentage.
View 11 Replies
View Related
Apr 19, 2013
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
View 1 Replies
View Related
