Showing Percentage In A Query
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 Replies
ADVERTISEMENT
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
Mar 11, 2014
I created a table called weld_performance. it consist weld_id, weld_prod, total_rt, accepted, rejected, and rejection rate. from weld_prod until rejection rate, the type data is number. the rejection rate field size is Single, and Format is Percent.
Based on this table, i create a query called query1. and based on this query1, i create a chart. please see attachment pic001. as you can see the data label on the chart is showing 0.66667. but in my query1, the value is 67%. if i click the value (67%) it's change to 0.66667.
So I guess, the chart is read the 0.66667 value from the query. now what i want to ask is, how can my chart data label is shown 67% instead of 0.66667 ??
Environment: Windows 7, Ms. Access 2010
View 2 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
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 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
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 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
Mar 27, 2013
I have this table
Year AvgOfValue
2005 109.061690295772
2009 106.801581389669
I have to calculate the percentage of change of the values. I have very basic knowledge. How can I make a query to calculate the percentage of change?
View 2 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
Apr 9, 2014
I obtained data for earnings by industry for men and women over time (5 years). I developed crosstab query that showed the average earnings for men and women for the 5 years.
Now I want within this query to calculate the differential between men and womens average earnings and calculate a percentage within this crosstab query. I tried to use Expressionbuilder with little luck.
View 6 Replies
View Related
Jun 1, 2005
Hello
I have a problem that is really giving me great headaches. I have had no success what so ever. My gratitude goes to anyone that can provide some insight.
I have two tables. One table for appointments and one table of all available time slots (ie 0900 is record 1, 0930 is record 2, 1000 is record 3 and so on).
If lets say there are records in the appointments table for eg today 01/06/2005 where there are two appointments (eg 0930 and 1000) I want to build a query that shows all the appointments that have not been used (so 0900, 1030, 1100,and son on). Adding to this, I need it to show these available timeslots for multiple dats (as the appointments table will have some appointments booked for the 01/06/05 and some for the 02/06/05 and some for eg 10/06/05).
I am aiming to implement this on a Active server page where the user selects the data and then the available appointments are displayed for that given date. However, I know that the query as descibed above is essential for this to be developed!
I have spent many hours trying to solve this with absolutely no success, so many thanks in advance to anyone that can help. Apologies for descibing the problem thoroughly.
Thanks
Ricky.
View 1 Replies
View Related
Aug 2, 2005
Does anyone know how to do a query so that the user can find all the things that will expiry at the end of the current month?
The user will click on a command button and it will show the results of a product that will expiry at the end of a current month.
Have tried with parameters to which the user manually types the end date in and then it will show the results but having problems.
Cheers
View 4 Replies
View Related
Dec 17, 2007
SELECT Training.ID, Training.StaffPIN, Training.Module, Training.DatePassed, Training.DateExp, Training.Comments
FROM TrainingModules INNER JOIN Training ON TrainingModules.[TrngModule] = Training.[Module]
WHERE (((Training.StaffPIN)=[Forms]![TrainingFrm]![StaffPIN]));
Does anyone know why this query isn't working? It doesn't throw an error, it's just not showing any records.
The only thing I can think of is:
Training.StaffPIN is a long int
When it prompts me for [Forms]![TrainingFrm]![StaffPIN] I enter '12177' in the message box, is that taken to be an int or is it actually a text string?
It might not be this as when I use the form [TrainingFrm] to pass the perameter, it's taken from a combobox ([StaffPIN]) where it IS an int, and the query is still blank. :confused:
TrainingModules is a table that stores all the possible modules or subjects that a member of staff can be trained in
Training is a table that stores which staff member has passed which module or subject.
I want the query to pull all the info on a staff member from Training to be later used as a subform.
View 5 Replies
View Related
Nov 11, 2004
Hello. This is a very basic question. I have a query that has relationships set within. Now.. When I pull up the query with a criteria such as "date" ... the query pulls up all the information for only the items that have all the related fields filled in. How would i make this query show ALL the items from that "date" even if their related fields are blank or there are no relationships in another table?
Please let me know.
OvAdoggvO
View 1 Replies
View Related
Feb 6, 2007
Hi All
I have a feeling that this is an absolute newbie question.
I have three queries, qryVisitsDue, qryVisitsOverDue, qrySiteView.
The query qrySiteView uses SiteID to produce a listing of all sites which includes a count of visits for those sites. If there is no visit data the site does not display. To display the counts I'm using the other two queries. In the first two I'm querying a table, tblVisits, to find what visits are due or overdue based on the current date. They work fine where there is visit data. My problems start when there is no data for a site. I need to be able to show the sites where there is no visit data.
I'm not very experienced so basic explanations are probably the best.
Any help much appreciated.
ChrisD
View 5 Replies
View Related
May 25, 2007
I want to use a column in a query to show where data is missing in other fields.
In excel I have used this statement:
=IF(COUNTA(I5:J5)=2,"","error")
Basically, I have two fields PRICE and WEIGHT. I want a column in the query to show 'Error' (or any kind of flag) when either (or both) of these fields are blank.
Hope this makes sence.
Any advice?
Thanks
View 1 Replies
View Related
May 27, 2007
I have two tables, bad design, not my choice of the way it's set up.
Table a has Fields 1-2, dups allowed in all fields. No pk was originally assigned.
Table b also has the same fields 1-6, but also has field 6.
Table a has all of the records in question, table b only has some of the records from table a but does not have any records that are not in a.
Something like this.
Table a
Field1 Field 2 Field 3 Field 4 Field 5
John Shirt blue denim button
Mark pants green straight
John Shirt blue silk button
Table b
Field1 Field 2 Field 3 Field 4 Field 5 Field6
John Shirt blue denim button new
Mark pants green straight used
If I query the two tables, a left join to b, then I get the correct number of total records. If I select any records from b, then the total records goes higher than there are, even for the two combined, listing the same record multiple time.
Any suggestions are appreciated. I am a blood bank lab person, not an IT person, or DBA, so simple responses are greatly appreciated.
View 6 Replies
View Related
Oct 27, 2007
I have make relational table on query, but some record not sohowing on query, although i have the record on table.
for example, i can't see the record with code :
131401.007
541000.00*
is the anything wrong?
thanks
View 3 Replies
View Related
Nov 19, 2007
Is it possib;e to create a query that would all the dates withn a range of Date1 and Date ?
Ex if I have
Filed Date1 Date2
x 1/1/07 1/3/07
c 1/6/7 1/8/7
for query to show
Field Date
x 1/1/7
x 1/2/7
x 1/3/7
c 1/6/7
c 1/7/7
c 1/8/7
View 1 Replies
View Related
May 3, 2008
Hello all, I am trying to dispay results in a query which is based upon time.
e.g.
Sample data - class and class start time
Class one - 7pm
Class two - 7pm
Class three - 7pm
Class Four - 8pm
Class Five - 8pm
Class Six - 9pm
Class Seven - 9pm
Class Eight - 9pm
Class Nine - 9pm
If i was using the sample data above and the time was between 7pm to 8pm i would like to show only the classes which started at 7pm, or if the time was 8pm to 9pm, only show the classes which started at 8pm and the same for 9pm
I hope i have explained myself clearly, and I will be thankful for any help.
Thanks
View 4 Replies
View Related
May 26, 2006
so I have been working on this form for a while and I'm really a newb at this
maybe it easy maybe its not but i just couldn't figure this out
I'm so frustratated that I'm willing to pay(beer money for help)
well anyways I have this form
http://i27.photobucket.com/albums/c176/rlan214/WorkAssignForm1.jpg
at the side you can see that there are 3 filters
1st has check box (check box is always checked) for the date
2nd check box for Target Location
3rd check box for ForkliftID1
I also don't know how to change the date format in my date
its in yyyymmdd format right now and I want it mmddyyyy
In the middle theres a blank, and thats where I wanted my
query to show
here's my query
http://i27.photobucket.com/albums/c176/rlan214/WorkAssignQry1.jpg
View 1 Replies
View Related