Find difference of values between dates

[Miquel1]

I am trying to find the difference between weights of a group of animals between two dates – for example, I want to find the difference in weight for the animal “AA1999” between the dates 01/01/99, 01/06/99 and 01/01/00. I can find the difference in weight between the first and third dates fine (using FirstDate and LastDate), but it’s the difference between the first and second and the second and third dates I can’t work out. I could do it manually, but I have over a thousand animals for which I need the data and some of these animals have up to 10 weights.



All the data is in a single table with the fields [AnimalID], [DateWeighed] and [Weight].

I have attached a data base with a sample table.

Thanks all for your help.

Miquel

[ItsMe]

Maybe something like this could be used behind a form.

Dim dtStart As Date
Dim dtEnd As Date
dim strWhere as String

dtStart = Me.StartDate.Value
dtEnd = Me.EndDate.Value

strWhere = "(qryName.DateField) BETWEEN " & "#" & dtStart & "#" & " and " & "#" & dtEnd & "#"

[ssanfu]

Welcome to the forum


I found an example dB of "Previous Date Over Group - Using Correlated Subquery"

Is this what you are looking for???

[Miquel1]

Thanks Itsme and Sanfu,
I first tried Sanfu's example database, and this works well for when an animal has gained weight, but not for when an animal has lost weight, because of the "(SELECT max(weight)" part of the SQL - the next weight won't necessarily be more than the previous one as sometimes the animals' weight "yo-yo's" up and down.
I wanted top try Itsme's tactic, but as I'm a dirty newbie I don't understand what you mean by "behind the form". I'd assume that "behind the query" means SQL...

Any more ideas?

Thanks so much for your help so far!

Miquel

[ItsMe]

You can associate VBA code to a specific Form or Report. You can place VBA within a form's class module. The code I offered is a small part of some VBA that might offer a solution. You would first need a query that included all of the records you would be working with. You can base a form off of this query. With that, you would need to add some controls to the form. The User's interaction with the controls would create value's, such as dates, via user input. The user would also trigger events via the controls - maybe a button with a click event. Events can fire lines of code that are within the form's module - code specific to the form, specific to the control, and specific to the event handler.

You definitely need tables and queries to manage your data before you can create a form or GUI for the User to interact with. Maybe the sample DB offered by Steve can help you to ensure you have the correct relations before moving to the application part of your development.

[ssanfu]

I don't have A2010 right now to look at what I did (I should have posted the SQL), but I remember there were minus weight differences (the right hand column).

IIRC, the Max function was getting the max weight for the latest date for the animal, not the max weight of all of the dates.

If you look at the two weight column, the the first Previous weight column for an animal is (should be - obviously) zero/blank.
But if the first weight was 120, and the second weight was 150 ,then the second line of the Previous weight column would be 120.

I thought the Weight difference column had negative numbers, which would mean the weight went down from the previous weight reading.

I'll take a look at the db tonight

[ItsMe]

Steve,

You used Max Function for the Alias PreviousWeight

SELECT AW.animalID, AW.dateWeighed, AW.weight, (SELECT max(weight) FROM weights WHERE animalID=AW.animalID and dateWeighed < AW.dateWeighed) AS PreviousWt, [weight]-[PreviousWt] AS DeltaWt
FROM Weights AS AW
ORDER BY AW.animalID, AW.dateWeighed;

[ssanfu]

Miquel,
Apologies...it was late and I missed seeing the wrong values.

I couldn't get the query to return the correct values, so I resorted to VBA.

Since the calc uses a UDF, it might take some time to calc over a thousand animals.... but it is faster than doing the calcs manually

[Miquel1]

Hi guys,
So it worked (yay!). I'm not at all up on my VBA but I kind of understand what you did Steve. Thanks both of you, you've been a great help!
Miquel