Calculating percentage change from previous records.

Adsmit

New member
Local time
Today, 12:47
Joined
Oct 1, 2014
Messages
3
Hi,
I’m looking for a solution to the following problem. 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.


Thanks for your help everyone!


Ad
 

Attachments

  • weight.jpg
    weight.jpg
    37.9 KB · Views: 75
Last edited:
You will need a sub-query to determine the last date an animal was weighed:

Code:
SELECT AnimalID, Weight, DateWeighed, DMax("[DateWeighed]", "YourTableNameHere", "[DateWeighed]<#" & DateWeighed & "#") AS LastDateWeighed
FROM YourTableNameHere

Replace the instances of 'YourTableNameHere' with your actual table's name. Save that query as 'sub_WeightChanges'. Then, use it and YourTableNameHere in a new query. Link YourTableNameHere.DateWeighed to sub_WeightChanges.LastDateWeighed. That will link the current data with its prior record.
 
Hi Plog - thanks for the reply!
The issue I'm having now is that I receive a Type Mismatch in Expression error - initially I thought that this was because the date results in the second query displayed like 01-Sep-2013 for results from the Table and like 01/09/2013 for results from the query sub_WeightChanges. However, when I changed the date format in the table's design view to match, I still get the same error. Any idea?

Ad
 
Using a DMax in a query like that is extremly inefficient becuase it will be like running a separate query for each record.

Use a self join and let the database engine work efficiently.

Aircode so won't guarantee to work without being refined.

Code:
SELECT X.AnimalID, X.Weight, X.DateWeighed, X.NextWeighDate, Y.Weight AS NextWeight, X.Weight/Y.Weight AS Ratio
FROM
(
SELECT AnimalID, A.Weight, A.DateWeighed, Min(B.DateWeighed) AS NextWeighDate
FROM TableName AS A
INNER JOIN TableName AS B
ON A.AnimalID = B.AnimalID
AND B.DateWeighed > A.DateWeighed
GROUP BY A.AnimalID, A.Weight, A.DateWeighed
) AS X
INNER JOIN TableName AS Y
ON X.AnimalID = Y.AnimalID
AND X.NextWeighDate = Y.WeighDate
WHERE X.Weight/Y.Weight < .85
 
Last edited:
Hi, just for fun I put this together. It may not be what you want or be correct.
Someone more experienced no doubt could offer a better solution.
Was very tired and brain dead!
HTH
 
Last edited:
Hi guys,
Thanks for your help so far; Burrina, that's the sort of thing I'm after in the long run, but my main issue so far is trying to extract the second-to-last weight an animal gave. Glalaxiom, I tried your code (replacing the table name - my table is called tblWeights) but Access couldn't differentiate between AnimalID fields in the FROM clause...
 
I have fixed the shortcoming in my sql. It needed to differentiate AnimalID in the Group By clause of the subquery.
 
Sorry Burrina, that isn't what should be done.

DateWeighed is an aspect of the measurement, not the animal.
 
Well, perhaps this is, or at least one approach.

SELECT tblAnimals.AnimalID, tblAnimals.Animal, tblWeights.WeightDate, tblWeights.Weight, (SELECT T.Weight FROM tblWeights AS T WHERE T.AnimalID = tblWeights.AnimalID AND T.WeightDate = (SELECT Max(S.WeightDate) FROM tblWeights AS S WHERE S.AnimalID =tblWeights.AnimalID AND S.WeightDate < tblWeights.WeightDate)) AS PrevWeight, [Weight]/[PrevWeight]-1 AS WeightGain, [WeightGain]<-0.15 AS SignificantLoss
FROM tblAnimals LEFT JOIN tblWeights ON tblAnimals.AnimalID = tblWeights.AnimalID;

HTH
 

Users who are viewing this thread

Back
Top Bottom