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.
I have two numbers, i need to calculate how much percentage of one is the other. E.g
num_1 = 100 num_2 = 10 percent = 10%
Users enter a dollar amount (retainer) and my code should calculate the percent of the total proposal amount.
Actual Code
Private Sub cmdSubmit_Click() Dim intProposalTotal As Double 'if i use interger i get "overflow" error intProposalTotal = Nz(DLookup("ProposalTotal", "qryPropsalTotalForRetainer", "proposal_id=" & Me.proposal_id), 0)
[Code] .....
billing_retainer_percent is formatted to Percent with 0 decimals. In the actual table Type - Number, Format - Percent, Field Size - Single. What i get is 100% instead of 10%.
Basically I have a report that shows any 'Issues' that wasnt closed within the KPI Target.
I have the report working, but I simply want to do a count of how many days the observations overran the 'Target Date of Closure'.
The report also shows observations that are not closed but passed their Target Date of Closure. These observations will not have a 'Actual Closure Date', but I would like to still have a count of how many days its overrun closure.
This is a formula I tried to piece together but obviously not correct as it's not returning anything;
IIf(IsNull([Issues]![Actual Closure Date]),DateDiff("d",[Issues]![Target Date of Closure], Date()),DateDiff("d",[Issues]![Target Date of Closure],[Issues]![Actual Closure Date]))
I am wanting to display in a text box or on my report the total number or records in my database. Also I have some buttons that filter the results a little, I'm wanting to display the number after I hit the button(s). Would I just add the query to the end of each button I have?
NAME [NUMBER OF ITEMS] [PURCHASE DATE] Walmart 4 1/4/14 Walmart 2 1/5/14 Target 0 1/5/14 Lowes 3 1/5/14 Sears 1 1/5/14 Sears -1 (returned to store) 1/6/14
I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.
This is one way I've tried to write the SQL, but I keep getting an error...
SELECT Count([TBL-STORE].[NAME]) FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME] WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1) HAVING ([TBL-STORE].[NAME])="DISTINCT");
I also tried an Count(IiF( and can't get that to work either..
I am trying to create a query that has a self referencing running total based on the values (point totals) of itself (running total of values in the running total column that have already been calculated for all previous records) plus the total of new points being added in the current record, less the total of points being removed in the current record. This running total can never go below 0, if it does, the running total should restart at zero and add in only new points and begin the process again with the next records
I am able to do this in Excel in less than two seconds so I know there has to be a way to port this into a query. I've attached an excel example of what I am exactly trying to do
If it takes multiple queries to complete the required output I am ok with it. In my previous outtakes I have had up to 8 queries but just couldn't seem to do it..
"This query should prompt the user to enter two dates and than calculate the total amount paid for all invoices in the Amount field between those two dates (inclusive)."
Query is based on table Invoices.
I have created the query and dates part works fine. However,when I run Sum for the Amount field, instead of total amount query displays amount of each invoice between those dates. How to get just Total (amount of each invoice added up)?
I have a table of hours that have been worked by employees for each day of the week
[moh] (Monday's hours),[tuh],[weh],[thh],[frh],[sah],[suh] data eg (this is how I would like it to be inputted into the table) 7:24:00,7:24:00,7:24:00,7:24:00,7:22:00,0:00:00,0: 00:00
This equates to 36:58:00
I have tried
Total Hours: [moh]+[tuh]+[weh]+[thh]+[frh]+[sah]+[suh]
but I am struggling to get what I want in the right format.
How to record the initial data or a formula to format the end result.
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
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'"))
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.
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?
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.
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.
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.
I have a log in table that counts every time a user logs onto the database. Each time a user logs in it adds another entry to a table.
I have built a query to display the user name and date of log in so I could display this information in a report. The report is now getting rather long, and I am looking for a way to display each users name and have the total number of times they have logged in, not display each time they have logged in separately.
The Query has two fields "Agent Name" and "Logger Date"
The report displays the data as follows
Agent Name Logger Date User 1 26/5/13 User 1 26/5/13 User 1 27/5/13 User 2 28/5/13
What I would like to do is have a report listing each users name, with a column showing how many times they have logged on. e.g.
In one table, I have a few fields. One of the field is "ItemSequence" and another one is "TotalPcs"."ItemSequence" is where user key in the sequence number for one or more item. 5 example for possible content of "ItemSequence" is as following :
1) 7 2) 4,6,9 3) 5-9 4) 3,5,9, 23-25 5) 3-5, 8-10
"TotalPcs" is the total number of items key in to "ItemSequence". For the 5 example above, the related "TotalPcs" should be as following:
1) 1 (1 item, which is item 7 alone) 2) 3 (3 item which is item 4, 6 and 9) 3) 5 (5 item which is item 5, 6, 7, 8 and 9) 4) 6 (6 item which is item 3, 5, 9, 23, 24 and 25 ) 5) 6 (6 item, which is item 3, 4, 5, 8, 9 and 10)
For time being, the user have to count manually to get the "TotalPcs". I wonder is there a way to calculate the "TotalPcs" by programming?
i''m currently trying to count the amount of records that fall in each month in the date complete field. i am currently using a count query and it it bringing up the dates not the month.
Income | Outcome | Transport | Total Income | Date Total Income (N) = Income (N) + Transport (N-1) Total income equals income from that day + transport from previous day
Transport (N) = Total Income (N) - Outcome (N) Transport from previous day equals Total Income minus outcome
How can I calculate the transport from previous day to sum to next day total income?
I am having trouble with this running total. Let's say I have a well. I am trying to create a running total that calculates the total Uptime (or hours operational) for each well every month.
I attached a PDF with an example of what I am working with.
I have to create a query in access that will calculate two Date and time fields [Date & Time Left]/ [Date Returned], need to figure out between the two fields. Trying to identify when the rep returned the call and the number of business hours (6:00am - 4:30pm) it takes to return a message in Ms Access 2010.
The only issue is the calculation has to be done by time and so I have to calculate what time they left the message(so the difference between [Date & Time Left] and [Date Returned) [Date & Time Left] and when the rep returned the message which is suppose to be [Date Returned] but the problem with this field is the data entry is in date format (10/9/2014, 00/00/0000) of Date and not Date and Time like the [Date & Time Left] field, so I don't know what to do now. Not sure what to do now not a database that create or have allot of control over.
Looking to have a count function which calculate data in sense like if records found on 1-jan-2014 the it give answer as 1, same as records for 2-jan-2014 it should return 2 and so on .. in short the criteria must look and give same number for same dates starting from 1
