Calculating percentage change from previous records.

[Adsmit]

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

 

[plog]

You will need a sub-query to determine the last date an animal was weighed:

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.

 

[Adsmit]

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

 

[Galaxiom]

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.

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

 

[burrina]

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

 

[Adsmit]

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

 

[Galaxiom]

I have fixed the shortcoming in my sql. It needed to differentiate AnimalID in the Group By clause of the subquery.

 

[Galaxiom]

Sorry Burrina, that isn't what should be done.

DateWeighed is an aspect of the measurement, not the animal.

 

[burrina]

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