Queries :: How To Set Criteria For Percentage Calculation In SQL

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 Replies


ADVERTISEMENT

Queries :: Percentage Calculation In A Query?

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

Use Of Nz In A Percentage Calculation

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

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 1 Replies View Related

Calculation Criteria On Max(date)

Jan 16, 2006

My query selects all those who donated to a fund.The max on the date restricts all but their last donation, working fine.But now I want to list all those who have not donated anything in the last x days (user defined). I want to use Date()- dateField>30 but now it does not let me peform the query.
Any way I can do this in just one query? Thanks!

SELECT main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul, Max(main.dgDate) AS maxdate
FROM main
WHERE (((Date()-[main].[dgdate])>30))
GROUP BY main.dgTitle, main.dgInit, main.dgSurname, main.dgNo, main.dgStreet, main.dgShul;

View 4 Replies View Related

Problem With Calculation From Date Criteria

Aug 13, 2006

Hi, I have a membership database I'm working on and the problem I'm tearing what's left of my hair out over is this:

If the date of joining the club is <= 15th of that month then the renewal date is 1st of that same month in the following year.

However, if the date of joining the club is >15th of that month then the renewal date is 1st of the following month in the following year.

I hope that makes sense/ Example:

JoinDate = 03/08/06 then RenewalDate = 01/08/07

BUT IF

JoinDate = 16/08/06 then RenewalDate = 01/09/07

Any ideas on how I would go about doing this would be greatly appreciated.

I realise that I could simply use the DatAdd() function to add an entire year to the JoinDate, but as you see the criteria I have to work to is unfortunately not that simple.:(

Thanks hopefully.

View 3 Replies View Related

Percentage Queries

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

Combining Two Queries To Get A Percentage

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

Queries :: Show Only Percentage Of Records

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

Queries :: Percentage Of Change For Each Record

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

Queries :: Calculate Percentage In Query

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

Queries :: Getting A Percentage From A Text Count

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

Queries :: Percentage Change Between Records

Oct 25, 2013

I'm trying to calculate the percentage change between records/rows. I've searched around the internet and found 2 possible examples, but they are very specific to the individual's database and I'm struggling to understand.

Here are two examples:

[URL] ....

[URL] .....

I believe I need to create a query based on another query (which I can do). I would need to create a column in the new query that calculates the percentage difference, but what's the formula/contents of that column?

View 5 Replies View Related

Queries :: Running Sum Of Percentage Field

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

Queries :: Percentage Of A Count By Group

May 6, 2015

I have a very simple query which brings back a count of records. I would like to add a percentage column to this which shows a percentage by ClinicCode. I've attached some sample data and what the inteded outcome should be.

View 1 Replies View Related

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 1 Replies View Related

Queries :: Add Percentage Column To Crosstab Query

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

Queries :: Update Query Percentage Prompt

Mar 28, 2015

I need to do a update query with prompt to input a percentage.

View 11 Replies View Related

Queries :: Calculate Percentage Per Passengers In Query

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

Queries :: Format Text To Numeric Percentage

Jan 29, 2015

I have a text percentage that reads 28.0%. I want to convert to a numeric percentage that reads either 0.28 or 28.0%, preferably the latter. The Val function returns an error.

View 3 Replies View Related

Queries :: Percentage (Pass / Fail) In A Text Column

Jun 6, 2013

I have about 20 columns in a query and each row is a customer.

The columns are either pass/fail but are text fields and not a yes/no set up. I want to get the percentage of a pass in the column.

View 9 Replies View Related

Queries :: Show Total Of Query Results With Percentage?

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

Queries :: Calculate Percentage On Grouped And Filtered Data

Jul 20, 2015

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'"))

View 11 Replies View Related

Queries :: How To Get Total Numbers Of Groups And Express Them As A Percentage

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

Queries :: Calculating Percentage Change From Previous Records

Oct 1, 2014

I have a table with the following fields; Row; AnimalID; Weight: Date_Weighed (see attached screenshot example).

I would like to make a query that gives me the percentage weight loss/gain since the last time a particular animal was weighed. For example, animal AAA gained 100% between 2014/01/01 and 2014/02/01. Animal CCC gained 0% between 2014/02/01 and 2014/03/01.

I plan to use this to create a pop-up that informs me if an animal had lost over 15% of its bodyweight since the last time it was weighed, indicating either a loss of body condition, or that a female has given birth.

View 8 Replies View Related

Queries :: Selecting Related Image Based On Percentage

Aug 16, 2013

I have 2 table,

Table1 has fields
docno (text)
title (text)
progress (number)

sample data is like below:

doc-001 test1 90%
doc-002 test2 25%

Table2 has fields

sample data is like below:

id (number)
icon(attachment type)

1 image1
2 image2

Now I would like to make a query when progress of each docno is equal and more than 50% it uses image1 And when progress is less than of 50% it uses image2

Doc-001 test1 90% image1
Doc-002 test2 25% image2

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved