Queries :: Adding Percentage Field Into Query
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 Replies
ADVERTISEMENT
Sep 2, 2014
I have two collums, currentowed and currentpaid. i want to add another field to my report showing the percent of currentpaid to currentowed but I can't seem to be able to make it work. I tried:
=sum([currentpaid]/[currentowed]) and it doesn't work.
View 3 Replies
View Related
Jun 17, 2015
I am trying to make a query that give me an output of an aggregate percentage column, or running sum of percentage. Please see my example:
Month Sales in % Running sum percentage
Jan 5.03 % 5.03 %
Feb 2.17 % 7.20 %
Mars 1.28 % 8.48 %
I have the column Month and Sales in %, What do I have to do to get an output like the example over?
View 2 Replies
View Related
Mar 19, 2014
We're trying to create a database to read quotes from a system based on changes made to components.
We have the database set up to store the quotes happily. We're pleased with the input forms and data capture however we are struggling with a query to get useful data from the database.
I have a main quote data table listing all the required fields such as costs and supplier data for the quotes, a table storing components that may be changed as part of a quote and a table listing alterations that could be made to these components. Each quote could have a number of changes made to a number of components. All these changes are stored in a changes made table which lists the quoteID, ComponentID being changed and The AlterationID of the alteration being made.
I want to be able to input a varied amount of changes via a form and be shown a list of all quotes where at least one change matches. I've managed to get this far using a lot of OR statements however the complexity is introduced as we need to sort these by an extra column produced by the query displaying the percentage the changes made in the quote match the search input.
If a quote appears matches my changes and there are no other changes on the quote - (100%)
If a quote matches all changes I have input but I input 5 changes and the quote has 6 - (5/6 - 83%)
If I input 1 change and a quote matches but has 8 changes on the quote - (1/8 12.5%)
View 2 Replies
View Related
Mar 11, 2014
The calculated field converts all percentage marks perfectly fine with grades except 100 which returns to a stupid "E" . I've been trying all sorts and now give up.
Code:
Grade:IIf([Percentage]<20,"U",IIf([Percentage]<30,"G",IIf([Percentage]<40,"F",IIf([Percentage]<"50","E",IIf([Percentage]<60,"D",IIf([Percentage]<70,"C",IIf([Percentage]<80,"B",IIf([Percentage]<90,"A",IIf([Percentage]<100,"A+")))))))))
View 1 Replies
View Related
Mar 20, 2013
I'm using Access 2003...I have a query that searches a parts table by description:
Like "*" & [Enter in Part Description to search] & "*"
I need to add another search to this query, I added another field to the parts table call manufacturer. I add this field to the above query and added this parameter to it:
Like [Enter in Manufacturer] & "*"
I then added a manufacturer to one of the fields for test purposes. For some odd reason this doesn't work. If I take out the manufacturer parameter and don't enter in anything into the part description the query returns all the records. When i type a description into the part description field the query returns the proper records. When I add back the manufacturer parameter and enter through both parameters only the one record returns showing the test record instead of all of them. If I put a part description in and enter through the manufacturer parameter no records show when they should.
I've tried adding a test field to a different table and tried a different query using the like parameter. The parameter works in a field already in the table but won't work with the new field I added. I've done compact and repair.
View 6 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 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
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
Jan 4, 2013
I'm having difficulty in adding decimal amounts to a percentage expression.On a form in a text box I have a percentage expression of 73.38%. In a combo box I have a drop down menu of: 0.75, 0.5, 0.25, 0, -.25, -.5, -.75With another text box I would like to have the decimal amounts added to the percentage. For example, 73.38% + .5..Which will equal in the text box: 73.88%
View 2 Replies
View Related
Feb 18, 2014
I am trying to add calculations to queries based on columns in the query... it seems to randomly expect 'Expression' or 'Group by' as column types, and Im having to create 3 sets of queries following on from each other to de-dupe data and allow filters on calculated values.
Also I've got a function which turns a date into a quarterly cohort, e.g. Oct 2013 -> 20134. I use ot on a lot of dates. I created a VBA function, CohortQ used as follows in queries:
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 CohortQ([InputDate]))
In the VBA, InputDate is defined as a date
Code:
Function CohortQ(InputDate As Date) As Integer
If InputDate = 0 Then
CohortQ = 0
Exit Function
End If
[Code] .....
But when I run it on a date field, it gives me a data mismatch error. I can't step through as it's working on 600K rows.
If I put the function into the query,
Cohort: IIf Year([InputDate]) < 1990 or Year([InputDate]) > 2020, 0 Year([InputDate])*10+DatePart("q",[InputDate]))
it works.
View 3 Replies
View Related
Jun 24, 2013
I have qry with these fields: DateOfPayment and Ammount.
I would like to add another field with running total sum. I am trying this:
RunnTot: Format(DSum("[Ammount]";"qryCFSUM"; [DateOfPayment] <=#" & [DateOfPayment] & "#" );"0 000"" Kč""").
But It still does not work.
Example of my data in "qryCFSUM":
DateOfPayment
20.1.2013
31.1.2013
30.3.2013
Amount
1 2000 Kč
15 456 Kč
23 465 Kč
And what I would like to have:
RunnTot
1 200 Kč
16 656 Kč
40 121 Kč
View 14 Replies
View Related
Jul 2, 2013
I am looking to create a query that adds a new field to the resulting table. The field should be 'Yes/No' and for every entry the default should be 'Yes'. The query looks something like this:
Code:
Select .*, [here the new field]
From MyTable1
Union
Select .*, [here the new field]
Form MyTable2
The Union statement isn't really relevant, just for completion's sake.
View 6 Replies
View Related
Oct 10, 2013
I am working on updating another person access database, how to add a calculated field. I need to add a field to an existing table to subtract the date listed in one field from todays' date. It would need to return the answers in days
ie
10/10/13 - 10/1/13 = Return answer of 9 days
I have been told about DateAdd but I cant find these date function in the option of expression builder.
View 3 Replies
View Related
Jun 15, 2015
I have a query all set up and now I have to add one field from another table in it. I am looking for a date which has the criteria Now() - Last Movement Date. Last Movement Date is the column I am taking from the other table which I just added which is the ZLX02 table. When I run the query, everything but the Last Movement Date shows up. What can I do to get the Last Movement Date to show? Check out the attached pics.
View 5 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
Jan 1, 2015
There is a table with many columns. I want to add many rows to this table. The easiest way is to write a query to do that. But most of the columns have validation criteria, lookup tables, default values, nulls not accepted condition and what not. Where as my new records to be added have only the values in 4 columns and the rest are null. Is there a parameter that can be used to bypass all these controls and add the records? You are going to say "You will destroy the integrity and consistency of the database".
View 1 Replies
View Related
Jun 20, 2005
I just recently figured out how to count how many jobs a CSR has submitted and how many errors a CSR has made. What I need to do now is combine these two queries and figure the percentage of jobs correct that a CSR has submitted. I tried to combine these to queries in a report and figure it out that way, but that didn't work. When I combined the two queries I lost the individual information in the queries. I tried to combine the two queries but it wouldn't let me. It said that I was trying to combine the fields of a table and a query and that I should use one or the other.
Can anyone help me?
learnasugo
The excel file is how they would like the report to look
View 9 Replies
View Related
Jun 19, 2014
On a table I have a field labeled "Interest", and it is for what you would expect.
I have the data type set to Number, and the format set to Percent. For some reason I cannot change this field, it keeps resetting the value back to 0.00% even though I type something else in.
Access 2007
View 1 Replies
View Related
Nov 7, 2013
Is it possible to only show a percentage, like 5% of records using a query? Ideally i would like my query to show a random 5% sample from a table, that would be great. Is this sort of query possible?
View 3 Replies
View Related
Dec 11, 2014
I'm new to this and I've created a report that shows evaluation scores before and after for some training we are delivering. The Query I've created works out the percentage of change for each record. The problem I'm having is that I can't now summarize these percentages for each instructor.
What I'm entering to retrieve the percentage score is:
=(Sum(IIf(IsNumeric([Percentage Change]),[Percentage Change],0)))/(Sum(IIf(IsNumeric([Percentage Change]),1,0)))
What i get back is as follows:
Before score = 2.85 (correct)
After Score = 4.22 (correct)
Percentage of Change = 55% (incorrect this should be 48% )
View 14 Replies
View Related
Jun 11, 2013
I have a query that has clients sorted in the row area and a column from my table that is set up as text but only has yes/no options for the data. I have a pivot table that counts the yes's and counts the no's and finds a grand total; however, I want to be able to find the percentage of pass's given the grand total. I'm able to do this in Excel and was wondering if it was possible in Access.
View 2 Replies
View Related
