Count And Percentage

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 Replies


ADVERTISEMENT

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 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

Reports :: Calculating Sum (count) And Percentage Field

Jun 16, 2013

I am trying to work out the expression that will first count the number of 'Yes' returns in a series of yes/no boxes and then display as a percentage.

To explain...The yes/no boxes represent attendance over a 20 lesson course. I have added a count for each lesson for student attendance..(grouping them in the footer) but I would like to add the number of attendance for each student.

View 1 Replies View Related

Percentage Based On Count - Table With Name And Items Of Report

Sep 19, 2012

I have a table with the name of a report, the items on the report, and the number of items on the report. I would like to count the instances of the names of the individual reports that appear on another table and then divide that count by the number of items on the report.

View 1 Replies View Related

Queries :: Count Same And Differences / Calculate Percentage Of Number Of Same Over Total Amount

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

Count Records Problem. Display Field Even When Count Is Zero.

Apr 13, 2006

I have a table tblBookings.

In this table it has a bookingID, CustomerID and some other none relevant details.

The CustomerID comes from table tblCustomer. i.e a customerID must exist in the customer table to be allowed in the bookings table tblBookings

A customer can exist in tblCustomer without existing in the booking table.

I am trying to write a query that will list each and every customer ID in the tblCustomer and count the number of bookings that that customer has (even if it is zero).

I have a query that will count the bookings if they exist in the booking table and display the number of times that a customer appears in the bookings table.

SELECT tblBookings.CustomerID, Count(tblBookings.CustomerID) AS NoOfBookings
FROM tblBookings
GROUP BY tblBookings.CustomerID;


How do I create a query that will do this but list all customers even if they don't exist in the bookings table (but obviously occur in the customers table)

I am trying to create a similar query where all bookings per hotel are listed even if no bookings are made for that hotel. I am guessing the answer is the same as above.

The Ritz. Bookings 0
The Hilton. Bookings 3
The Carlton. Bookings 0
The Lowry. Bookings 2

For every hotel.

That kind of thing.

If you need more information please shout.

View 3 Replies View Related

Percentage Help

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

Queries :: Using Count And MIN Together To Retrieve Only MIN Record With Count

Aug 16, 2015

I have a table that has 5M+ accounting line entries. Below is an example of one accounting journal in the table.

BUSN_UNIT_IJRNL_DJRNL_ICNCY_CMONY_A
CB0014/07/20140002888269323AUD16797
CB0014/07/20140002888269323AUD-16797
CB0017/07/20140002888269323AUD16797
CB0017/07/20140002888269323AUD-16797

The journal ID above was an accounting entry, debit $16,797 and credit $-16,797. because it was entered as a reversing journal in the system, the table has captured the Journal ID with 2 dates. For my purpose i only want the one date (MIN) date, the total amount of the journal (either the debit or credit amount 16,797) and the total number of lines the journal ID has so in this instance I want the count to be 2 and not 4.

Right now this is what i get

BUSN_UNIT_I JRNL_I CNCY_C SumOfMONY_A CountOfJRNL_I MinOfJRNL_D
CB001 0002888269 AUD 0 4 4/07/2014

This is the output i would like

BUSN_UNIT_I JRNL_I CNCY_C SumofMONY_A CountofJRNL_I MinOfJRNL_D
CB0010002888269323 AUD16797 2 4/07/2014

Im thinking with the total sum because theres debits and credits is there a way to do the absolute value of the journal MONY_A then divide by 2?

current SQL
SELECT [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, Count([One Year Data Lines].JRNL_I) AS CountOfJRNL_I, Min([One Year Data Lines].JRNL_D) AS MinOfJRNL_D, [One Year Data Lines].BUSN_UNIT_I, Sum([One Year Data Lines].MONY_A) AS SumOfMONY_A
FROM [One Year Data Lines]
GROUP BY [One Year Data Lines].JRNL_I, [One Year Data Lines].CNCY_C, [One Year Data Lines].BUSN_UNIT_I
HAVING ((([One Year Data Lines].JRNL_I)="0002888269") AND (([One Year Data Lines].CNCY_C)="aud"));

View 9 Replies View Related

Percentage In Report

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

Maths/Percentage

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

Access 97 Percentage ?

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

Format As A Percentage With An Iif

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

Percentage In A Query

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

Percentage Problem

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

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

Percentage Expression

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

Calculate Percentage

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

Expression To Get A Percentage

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

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

Applying Percentage

Jan 30, 2007

Sorry I removed the post because i answered my own question by typing it up =)

View 1 Replies View Related

Percentage Function?

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

Getting Percentage As My Update Query

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

Percentage Of Males And Females

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

Convert Results To Percentage

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

Need To Add A Column That Would Calculate Percentage

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







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