PerCentage Loss/Gain Since Last Weighed

[burrina]

1. How can I get the percentage weight loss/gain since the last time a particular animal was weighed?
2. Next, if Animal has lost over 15% of weight since last weighed

tblAnimal
AnimalID PK
Animal (Text)


tblWeight
WeightID PK
AnimalID FK
aWeight (Text) Weight of Animal.
DateWeighed (Date Weighed)

Code: Select all

SELECT DISTINCTROW qryAnimals.AnimalID, qryAnimals.Animal, qryAnimals.aWeight, qryAnimals.DateWeighed, DateDiff("d",[DateWeighed],Date()) AS ByDate
FROM qryAnimals
GROUP BY qryAnimals.AnimalID, qryAnimals.Animal, qryAnimals.aWeight, qryAnimals.DateWeighed, DateDiff("d",[DateWeighed],Date())
ORDER BY qryAnimals.Animal;

[StuartR]

I can't tell you how to express this in a query (I don't do Access), but the formula to calculate the percentage change between two numbers (Num1, Num2) would be
100*(Num2-Num1)/Num1

[Rudi]

Like Stuart, I'm not to literate on SQL but in formula terms the second questions calc would be:

(Initial_Weight - Current_Weight) / Initial_Weight

[burrina]

Thanks Everyone. Almost got it,this is my latest attempt.

[imincorrigible]

I think you would want the animal number, the date and the weight as one record so you would know what the animal weighed on that date. This way you will be able to see the whole animal's history. The tblAnimal should have the animal only listed once. I am not sure how to compare the weight from one date to the next though. Must be a way. Hmmmm.

[HansV]

As Imincorrigible mentioned, date and weight should be in a single record.
Here is the table structure I propose:

S0105.png

To compare weights, you either need multiple queries (one to get the previous date an animal was weighed, another one to retrieve the weight from that date, and yet another one to calculate the weight gain/loss, or use subqueries to do it all in one step.

I have attached a sample database demonstrating the latter approach:

Animals.zip

[burrina]

Many Thanks, That was what I was after.

[imincorrigible]

Hi Hans. I am not familiar with the expression that you use for the prevWeight field. I am guessing that by using 'T' and 'S' that makes the typing less. What is that called when you select something as T or as S and then use T.WeightDate, S.WeightDate, T.Weight, T.AnimalID and S.AnimalID? Since I don't know what it is called I can't search to understand it's usage. It is really cool how it works. Thanks.

[HansV]

The T and S are so-called aliases. We can use an alias to save typing the entire table name repeatedly, but more importantly, it serves to distinguish the table used in the main query from the table (with the same name) in the subquery. In the field definition

PrevWeight: (SELECT T.Weight FROM tblWeights AS T WHERE T.AnimalID = tblWeights.AnimalID AND ...

T.AnimalID refers to the AnimalID field in the table of the subquery; this must be equal to tblWeights.AnimalID, i.e. the AnimalID field from the table in the main query. If we indicated both as tblWeights.AnimalID, it wouldn't work: WHERE tblWeights.AnimalID = tblWeights.AnimalID is always true, of course.

[imincorrigible]

Very interesting. Thank you for your fast reply.