Percentage Values On A Form
Dec 7, 2005
This is a very minor problem. But it's one of those things that will bug me to no end. I have a discount field on a pop up form whose format is set to percentage. The field it is bound to is also set to percentage. When the user entered, for example, 50, the field would change to 5000%. So in the AfterUpdate I divide the value by 100. This works fine. The issue that's got me stumped is the appearance of the value. When the value is 50.00% and the text box receives focus, the appearance stays 50.00%. When the value is 10.00% the appearance changes to 0.1. I've entered various values and some change and some don't. Would anyone know why?
View Replies
ADVERTISEMENT
Apr 8, 2014
I work at a program where we have individuals try out for a field (coming from various backgrounds). The program has been in place since 2012, and I wanted to start looking at the data for analysis and comparing trends, etc; I imported our master file (excel) into Access. It's in one big table, column headings include roster number, name, gender, type, FY (fiscal year), and finalcode (Select, Non-Select - but put it in as 1 or 2 in the field)).
I created a query, and was able to get data I was looking for.I then set it to Pivot Chart (bar graph).
Bottom (Believe its X) Axis: I have Type, Year.
Right Side of Chart (TypeCodeNumber)
Top of Chart: Count of CodeNumber
It gives me the totals (whether select or non-select) just fine, but I have been racking my brains, watched tons of YouTube videos and read a lot on how to make it give me a percent. I found a way to go Percent on the left side of the bar chart, but the Values go up to say 3300%. I know the problem is with the Count up top.
View 7 Replies
View Related
Mar 10, 2014
I'm trying to create an expression to calculate percentage.
The fields, Value A and Value B might both have null values, but I do not want Access to treat nulls as zero (as in the Nz() fn). How do I get the expression to exclude any nulls and not calculate for those rows, but set the calculated value for a row with null A or B to zero.
I want it to set no calculated value to zero so I don't get overflow errors and can therefore order the data by the calculated field.
View 1 Replies
View Related
Jan 27, 2015
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname]
=Sum([Quarter1_A]) - Named "Quarter_Total"
=Sum([Quarter1_T]) - Named "Quarter_Target"
=Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
View 14 Replies
View Related
May 4, 2006
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
View 1 Replies
View Related
Jul 20, 2005
I have a table that documents the result of unit inspections. The data is Date inspected (once/month), Unit, and about 12 yes/no fields.
I want to run a query that shows the percentage compliant (yes) of all of the categories per month. I also want to be able to graph the results for one year per month to show trends.
What is the best way to do this?
Thanks,
Jason
View 8 Replies
View Related
Aug 9, 2005
Hi, i have poduced a report and i want to calculate some percentages. Some of the fields i have are:
Total Sales Clothes Shoes Pants
200 20 120 60
% 100% 10% 60% 30%
As you can see in total 200 sales were made, 20 clothes sales, 120 shoe sales etc and below it a percentage of each of the sale types. I have put this ' =Sum([Clothes]/[Total Sales])*100 ' in to calculate the percentage...but it does not work...Any help would me much appreciated :)
*This hasn't shown up very well...hope you understand it?!
View 1 Replies
View Related
Jun 19, 2006
Hello, I'm stuggling to work out how to go about setting up a formula.
I have 497 students 186 are Male 311 are Female. How do i work out the percentage of Male and Female students? I have no idea how to do the Math behind this so cannot start to add it to my database. If anyone can show me how to calculate this it would be really helpful.
regards
Pete
View 2 Replies
View Related
Dec 3, 2007
I need to be able to store percentages in an Access 97 table. I have set the data type to double and 4 decimal places, but when I enter .14, Access rounds to 0 and stores it as zeros.
What am I doing wrong?
View 2 Replies
View Related
Jul 4, 2005
In one of my queries I calculate a percentage based on a change from one year to the next; however, sometimes there will not be any prior year data hence the percentage is undefined (i.e. current year amount/0=undefined). To stop the query from displaying an error when this is the case I wrote the following:
CapitalSurplusChange: IIf([P/Y Operating Data]![CapitalandSurplus]=0,"N/A",(Nz([C/Y Operating Data]![CapitalandSurplus],0)-Nz([P/Y Operating Data]![CapitalandSurplus],0))/Nz([P/Y Operating Data]![CapitalandSurplus],0))
I have the properties of the field set to percentage, but the calculation does not display as a percentage but as a long decimal. If I take out the Iif the percentages display fine however there are errors for the undefined calculations (hence why I inserted the Iif). How can I amend the code above to only display the "N/A" answers as text. I know about the formatpercent function, however I am exporting this output to excel and this when using the formatpercent or formatnumber operation the data still displays as text and not a number. Any ideas?
View 3 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
Dec 6, 2005
Hi,
I have a database which contains the following column: -
TYPE OF COVER
This column has various covers in it i.e. 'COMP', 'TPFT', 'TP' etc
I would like to be able to run a query that shows the following columns: -
TYPE OF COVER , TOTAL NUMBER OF EACH COVER, PERCENTAGE TOTAL
I am new to SQL and would be grateful for some advice.
Regards
View 1 Replies
View Related
Jan 16, 2007
I have a Query that gives me the output as follows:
Code___DateofReview___Response___CountofReponse
AAA____12/19/2006_____0_________3
AAA____12/20/2006_____1_________2
AAA____12/20/2006_____0_________1
AAA____1/4/2007_______1_________2
The Field "Response" can either be 0 or 1. The Variable "Countofresponse" gives me the Count of response when it is 0 and 1 for a given Date of Review.
How do I modify this query so I can get a Percentage value added to this list. I would like to see the following result but not sure how to arrive at this:
Code___DateofReview___Response___CountofReponse___ PercentValue
AAA____12/19/2006_____0_________3________________100%
AAA____12/20/2006_____1_________2________________66%
AAA____12/20/2006_____0_________1________________34%
AAA____1/4/2007_______1_________2________________100%
Can anyone help please?
View 3 Replies
View Related
Aug 15, 2007
I have about 40 queries based on employed trainees from different groups(MMF, MKO, CNP,...). Now, what I'm trying to do is find the percentage of trainees employed from the total trainees, % employed in community from total trainees, % employed at wuskwatim from total trainees,... and so on.
If you take a look at my DB, you will find I have have MANY queries made. Is there an easier way to find this kind of information?
Any suggestions would be appreciated.
18448
View 3 Replies
View Related
Oct 24, 2007
Hello All,
I am not sure what I am doing wrong. I am trying to get a percentage of two numbers and the expression is not comming out right. This is the expression:
Expr1: [Ontime PU.CountOfP Performance]-[Late PU.CountOfP Performance]/[Ontime PU.CountOfP Performance]
Here is what it should do:
1242-68/1242=0.94524959742351046698872785829308 (which would be 95%)
For reason it is not doing this?????
Any Ideas????
Thanks
View 11 Replies
View Related
Mar 22, 2008
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.
View 1 Replies
View Related
Jan 22, 2005
For several days now, I have been trying to write an expression, in a report, to get a percentage. I am new to Access so I don't completely get it yet. This is the expression I wrote (only works sometimes):
=[pathjbo50]/([pathjbo50]+[pathcol50]+[pathpp50])
This expression only works when all the fields are populated. If any of the fields are empty, I get nothing.
Please help!
View 2 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
Jan 30, 2007
Sorry I removed the post because i answered my own question by typing it up =)
View 1 Replies
View Related
Dec 31, 2007
Hello all,
Hope everything is well! I have a query result for a period time to return group by of a list of Work Orders and sum the work hours charged to each work order. How do I also get a PERCENTAGE of the hours charged to each Work Order within that period. For example, my query result have the following data. Is there a Percentage FUNCTION to calculate the PERCENTAGE so it can give me the result automatically?
In the example below, the PERCENTAGE work hours of Work order 71820017 is approximately 18% (resulting from 80/440); of 71820100 is 23% and of 71820200 is 59%? How do I get these 3 PERCENTAGE results in an additional colum in this query result or I need another query and please advise HOW TO?
Work OrderSumOfHoursWorked
7182001780
71820100101
71820200259
Please help! Thanks so much,
Happy New Year!
View 3 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
Jun 6, 2005
how would i work out the percentage of males and females in my database?
i have done three queries.. one to count everyone, one to count males and one to count females.
is there an expression that could calculate this?
something like [Query:Count Males]/[QUERY:TOTALCOUNT]*100
View 12 Replies
View Related
Aug 29, 2005
I have a query which is based on another (SearchCriteria2)...the SQL is as below:
SELECT SearchCriteria2.[Problem Source], Sum(SearchCriteria2.[CountOfProblem Source]) AS [SumOfCountOfProblem Source]
FROM SearchCriteria2
GROUP BY SearchCriteria2.[Problem Source];
This gives an output like this:
Problem Source...................SumOfCountOfProblem Source
Authorisation......................1
Availability.........................363
Back Order Report...............64
Breakage...........................2
Collateral Order...................25
Complaint..........................3
Credit...............................7
What I'm trying to do is add another field to this query which will express the "SumOfCountOfProblem Source" column in percentage terms so I can eliminate those below 1%.
This would give a result like this:
Problem Source..................SumOfCountOfProblem Source........Percentage
Availability.........................363.......... ................................8.06451613
Back Order Report...............64........................... ................13.76344086
Collateral Order...................25........................ ...................5.376344086
Credit................................7........... .................................1.505376344
Can someone please give me some inspiration?
Thanks in advance
View 2 Replies
View Related
Oct 26, 2006
Does anyone know how to add a column to an existing table that would calculate percentage?
Thank you very much in advance!!!
View 3 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
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