I'm not sure where to start here, I have a table with the fields “GoatID”, “DateWeighed” and “Weight” which is populated as follows

GoatA, 01/01/2010, 5
GoatB, 01/01/2010, 3.6
GoatC, 02/01/2010, 4
GoatB, 10/01/2010, 7.5
GoatA, 21/01/2010, 11
GoatC, 22/01/2010, 9.5
GoatD, 22/01/2010, 2.4
GoatB, 30/01/2010, 12

There are two queries that I would like to make:

1. A query that returns the most recent weight of the goats as follows

“GoatID”, “RecentDateWeighed”, “RecentWeight”

GoatA, 21/01/2010, 11
GoatB, 30/01/2010, 12
GoatC, 22/01/2010, 9.5
GoatD, 22/01/2010, 2.4

I suspect this has a relatively simple answer that has been detailed in another thread, if you can point me to it, that would be great.

2. From the second query I would like to get the “Average daily weight gain since previous weighing (DWG)”. Where DWG=(Difference in weight)/(Number of days between weighing), and Null if the goat is yet to be weighed twice.
So for the above data the result would be as follows:

“GoatID”, “DateWeighed”, “Weight”, “DWG”

GoatA, 01/01/2010, 5,
GoatA, 21/01/2010, 11, 0.3 {=6/20}
GoatB, 01/01/2010, 3.5,
GoatB, 10/01/2010, 7.5, 0.44 {=4/9}
GoatB, 30/01/2010, 12, 0.225 {=4.5/20}
GoatC, 02/01/2010, 4,
GoatC, 22/01/2010, 9.5, 0.28 {=5.5/20}
GoatD, 22/01/2010, 2.4,

I have used the “datediff” operation before but I don’t know how I could possibly implement it here. I’m just getting started in access so I may not have even set up the initial tables as would be required to return this type of information.

Any advice or suggestions are much appreciated.

Use a toals query Grouping by GoatId
Max the weighing date
Min the weighing date
Max the weight
min the weight
Alias a field to use the datediff to get total days
Sum the Weight
Alias a field that gets the average (sum of weight / total days)

*Using the number of times weighed in your average will not give you the correct average, you need to average the toal weight over the total daterange to give you an average wight over the period.

David

Thanks David

Unfortunately I am not looking for the average weight gain per day from the first weighing to the last but rather the average weight gain for each period between weighing.

This is needed to show the changing average weight gain per day as the goats get older.

ie for a single goat the average weight gain might be:
100g/day when it is less then one month old
50g/day when it is 2 to 6 months old and
20g/day when it is 7 to 12 months old

Thus I can't look up the max and min values but must look at each weighing sequentially.

Any thoughts?