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.
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?
I'm trying to create an expression to calculate percentage.
The fields, Value A and Value B might both have null values, but I do not want Access to treat nulls as zero (as in the Nz() fn). How do I get the expression to exclude any nulls and not calculate for those rows, but set the calculated value for a row with null A or B to zero.
I want it to set no calculated value to zero so I don't get overflow errors and can therefore order the data by the calculated field.
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:
I have the following query, which gathers a set of kpi for an employee across two reporting periods.
Code: PARAMETERS [Enter_ID] Long, [first_period] Long, [second_period] Long; SELECT * FROM kpi WHERE PERIOD_ID in ([first_period], [second_period]) AND Manger_ID = [Enter_ID];
What I hope to accomplish is to calculate the change from one period to the next. So, say we had Jun 2013 and July 2013 as reporting periods I would want to subtract each KPI in June from the same KPI in July and report on change in that value, for each employee ID. Is this something that can be accomplished with SQL or would I have to use VBA to do this?
For simplicity we will say the only fields in the kpi table are EMP_ID, PERIOD_ID, Manager_ID and the 8 KPI: X1, X2...X8
I don't use Access too often but I'm trying to connect a table to some business intelligence software I use.
A. Date B. Price C.Ydayprice 01/01/2015 101.45 02/01/2015 104.70 03/01/2015 103.00
Simple stuff. Once I've connected to the table, I can easily do what I want with Field 'Price' and 'Ydayprice'. However, I can't calculate column C. All I want is row 2 to say 101.45, R3 to say 104.7 etc. I've tried various things but I don't have the knowledge to write the action I require. I don't want to calculate the change in Access either.
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?
I have a column "CAT" each time that CAT switches between 0 and 1, I would like my query to auto-create a "grouping" and increment the group by 1. What I am looking for is the output as shown below.
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.
I use Duplicates Wizard to run about 14 queries which will show all possible combinations of duplicated records in an access table. Can anyone tell me how to add an extra column in reports 2 onwards displaying either a "yes" or a blank cell, being the answer to the question "does this record appear in any previous reports?"
I have a query that must calculate the different between values in two different records, based on the date.
The underlying ORIGVALUES table contains:
Date; Value; Diff 1/10/15; 100; (this is what I am trying to calc) 1/11/15; 101; (this is what I am trying to calc) 1/14/15; 102; (this is what I am trying to calc)
What is the best way to have each record calculate the selected date value divided by the prior date value)? E.g.
For 1/11 the math is 101 / 100 (so the 1/11/15 record would show the result of 1.01) For 1/14 the math is 102 / 101 (1/11 to 1/14 represents a weekend)
I could copy the prior day values to the current day records and do that math easily but that is very redundant.
I've been trying very hard to get through the various instructions, code, modules, etc, on calculating workdays. I am having no luck in getting this done. As one who can write an Access database only to the point of a little VBA, using mostly queries and formulae to get by, I have difficulty with posted code from time to time.
For example, in a previous thread (actually, in several previous threads), code was posted with the instruction (essentially), "Here. Use this." To which someone replies, "Thanks, worked a charm." Or the like. As one who struggles and has limited responsibilities in this area (and little time to learn while on the job), posted code can be a blessing... but "Here. Use this." can be its attached curse. "HOW do I use it?" is the next question. Is it a module? Do I put it as code on the On Open event for a form? Is it an expression I build into a query? Sometimes, seeing the code is only the first step... and for one like me, can lead to a lot of trouble.
Also, many have been kind enough to post databases, which show the end result of much work. This is also greatly appreciated, believe me. But, again, once I start looking to HOW it was built... the investigation sometimes leads nowhere. Where do I look for code? Did I miss something? Why doesn't mine work like that?
So, for the question I have now, I have searched this and other forums. I have copied and pasted code that I thought should work. I have copied code into a module and tried to call up that module in a query. I have followed instructions as well as I could... but you can guess where this is headed. What I have is, in the simplest form, a table called Employees with the fields Name, StartDate and EndDate. I need to know how many workdays there are in the date range of StartDate to EndDate, including the dates in those fields.
If you have answered this question before, I apologize for not being able to figure this out. Any help is greatly appreciated.
I have a "make table" query. Inside that query there is a simple expression that divides two fields by one another to arrive at a %. Example 5/100=5%. However, the data returned is not in a % format.I know that I can go into table properties and change the fromat to "percentage", but that will only work for the current table that I have created. Once I re-run the "make table" query, I lose the change to the format, and my value again returns to a number, not a %..
Is there any way to always have a percentage returned without having to manually change the format each time that I run the query, or having to make a "delete" and "append query" ?
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.
I am using the following UNION QUERY to total up equipment tested for a report.
SELECT "Laptops Tested" AS PCEQUIP, Count(*) AS RECORDS FROM [LAPTOPS] WHERE (((Date)Between [Enter Start Date] AND [Enter End Date])); UNION SELECT "Workstations Tested" AS PCEQUIP, Count(*) AS RECORDS FROM [WORKSTATION]
[code]...
I have created a report using ACCESS 2000 for this union query and it satisfies the requirement. I am trying to add the proper code and syntax in this query to total the number of all of this equipment tested.In this case the total would be 86. Is this possible?
I have a table (tbl Team Info) which contains names and codes for teams within my business (>400 records) and another table (tbl Process) which contains a list of high level tasks (30 records).
I need to create something where for each team name 9in tbl Team Info) I can map them to the tasks that they undertake (in tbl Process) and assign a percentage of time then spend on each task. Each team could map to several different tasks.
I want a report to tell me a percentage of records. i have a report that records "records that are past date". I want the report to tell me the percentage of total records that are past date. I have a query that shows only past date records and a query that shows all records how do i do this?
I'm trying to create something like an online banking view that shows the running balance as each transaction occurs. I have tblTransactions with fields AccountNumber, ItemDescription, and TransactionAmount. I'm trying to create something that shows these three fields and a fourth field with the running balance.
So if I initially deposit $100 it will show the first record with TransactionAmount = $100 and RunningBalance = $100. Then the next transaction will subtract the Transactionamount for the new record from the RunningBalance from the previous record to get the RunningBalance for the new record. So if I make a purchase for $2, the AvailableBalance for that record is $98. Is this possible with a query? Here's a picture to describe what I'm talking about ....
I have an unbound form with a combobox which needs to be validated before the control is updated.In the Before Update event I check that the value is valid and need to reset the value back to the previous value if not valid and then change focus to another control.I thought that the following code would work but the selected value remains in the control and I cannot move the focus to another control.
Code: Private Sub cboYears_BeforeUpdate(Cancel As Integer) Dim dteNewDate As Date dteNewDate = DateSerial(Val(Me.cboYears), Month(Me.txtCalendarHeading), 1)
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?
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
So I have a report with the following text box controls:
[Surname] & ", " & [Firstname] =Sum([Quarter1_A]) - Named "Quarter_Total" =Sum([Quarter1_T]) - Named "Quarter_Target" =Val([Quarter_Total])/Val([Quarter_Target]) - Named "%Target" (Percent Format)
The report is grouped by the expression '[Surname] & ", " & [Firstname]'.I am trying to sort the records by the %Target text box. I tried entering the expression into the sort function but it still sorts by the grouped expression. I also tried sorting by the name of the text box but got the same results. How can I sort by the desired control?
