Averages in Access query based on fields in multipleobjects and multiple date fields

[johnsmith1]

Hello friends,
I have a problem with Access that has proven to be well beyond me. I have uploaded a sample of my database with this post. I kindly request for help on the following:
Problem: I would like the query named "qry_Nesting and Temperature" to have calculated fields that return the incubation temperature of each nest from the temperature data in the table named "tbl_Temperature".
More specifically, I would like to have the following calculated fields in "qry_Nesting and Temperature":
1) AverageIncubationTemp : It should return an average of "Temp_C" from all "Temp_C" values in "tbl_Temperature" whose "Date_Time_Observation" is Between "DateTime Laid" and "DateTimeHatched" of "qry_Nesting and Temperature". This would give the average nest incubation temperature for each record in "qry_Nesting and Temperature"


2) AverageIncubation_1stHalf: This would give an average of "Temp_C" values just like above, but will only use "Temp_C" values whose "Date_Time_Observation" falls in the first half of the incubation period (Defined by the interval "DateTime Laid" to "DateTimeHatched"). This would give the average nest incubation temperature for the first half of the incubation period for each record in "qry_Nesting and Temperature"
3)AverageIncubation_2ndHalf: This would give an average of "Temp_C" values just like above, but will only use "Temp_C" values that fall in the 2nd half of the incubation period (Defined by the interval "DateTime Laid" to "DateTimeHatched"). This would give the average nest incubation temperature for the second half of the incubation period for each record in "qry_Nesting and Temperature"

This has been such a big challenge for me and I will appreciate all assistance I can get.
Thanks.

[June7]

There are 42 nest records and 2387 temperature readings. I don't see any relationship between the two tables. How do we know which temperature reading goes with with nest? This is a deadly flaw in the db structure.

[johnsmith1]

The temperatures are not nest temperatures, but atmospheric temperatures. We don't have nests every day, and the frequency of daily temperature recordings is also variable. As such, the number of nests and temperature observations don't have to be equal: Since, hypothetically, we can have the average temperatures for each of the 42 nests from only those Temp_C values whose dates of observation is within the nest's incubation period (I.e between Laid and Hatched date). I agree that there doesn't seem to be an obvious common field between the two tables. However, date is common. So how can we write a query to calculate average nest temperatures for each nest (Each of which has DateLaid and DateHatched value) from only those Temp_C values whose observation date is between the Date Laid and DateHatched of that nest??

[June7]

Oh, okay, I think I can work with that!

Try:
SELECT tbl_TurtleNestingData.*, DAvg("Temp_C","tbl_TemperatureData","Date_Time_Obs ervation BETWEEN #" & [DateTimeLaid] & "# AND #" & [DateTimeHatched] & "#") AS AvgTemp, DCount("Temp_C","tbl_TemperatureData","Date_Time_O bservation BETWEEN #" & [DateTimeLaid] & "# AND #" & [DateTimeHatched] & "#") AS CountReadings
FROM tbl_TurtleNestingData;

Domain aggregate functions can be slow in queries or in textbox ControlSource, especially with very large datasets but yours seems to handle it okay. Could also use DMin, DMax, DStDev, DVar. Sorry, no functions for median or mode.

Your fields DateTimeLaid and SuccessRate had space in the name. I removed them.

[johnsmith1]

Thanks June7, it might take me a while to proof test this, Ill get back to you on it as soon as I establish its accuracy.

[johnsmith1]

Hi June7
I have been running through the temperature averages computed by the query. Am afraid something is wrong with the “AvgTemp” values returned by the above code. If I copy and paste all temperature values whose “Date_Time_Observation” is within the incubation period of a nest to Ms Excel then get their average, The result should be equal to that nest`s “AvgTemp”.
For example:

1. NestID: 2011_NE_048 has “AvgTemp” value of 66.9373532450457. While, the average of the temperature values that were recorded between 03-Jul-11 and 09-Sep-11 (Incubation period of this same nest) from the temperature table is 66.6136159280669. The two results should be equal.

1. NestID: 2011_NE_016 has “AvgTemp” value of 72.0842911877395. While, the average of the temperature values that were recorded between 10-Apr-11 and 10-Jun-11 (Incubation period of this same nest) is 69.613. The two results should be equal.

This problem seems to be with all the records in the query resulting from the suggested code. I can’t figure out the cause of the discrepancy.
Thanks.

[June7]

I just ran the query with the data provided in posted file. Cannot replicate your issue.

NestID 2011_NE_048
Access AvgTemp: 66.6136159280669
Excel AvgTemp: 66.61361593

NestID 2011_NE_016
Access AvgTemp: 69.61
Excel AvgTemp: 69.61

[johnsmith1]

I really am not able to figure out where the problem is, here is an upload of my "malfunctioning" query. If you run this and have the right results, could it mean that my Ms Office installation is malfunctioning?

[June7]

I get the same results for both Access and Excel as I show in previous post.

Try running Compact & Repair of the db first (although I did not have to). If still discrepancy then I guess bad install is a possibility. Never seen anything like this.

[johnsmith1]

I have run compact and repair so many times by now. I still have the problem. And I too have never seen anything like this. Ill rebuild the database from CSV exports of the tables used in this query and see how it goes. Thanks June 7.

[June7]

Guess couldn't hurt to try rebuild but if corrupt project were the cause, seems I would also not get valid calcs. Good luck.

[johnsmith1]

Hi June,
Rebuilding didnt work and no amount of office reinstalls and coaxing could correct the problem. I have the problem in two computers, but I tried it on another computer, it worked and I exported the result to excel before anything could go amiss again. I verified my results. I would say am all set for now: will try to clean as much as I can on the two computers with the query problem: Whicj is the wierdest of all issues I have encounterd in MS Access. One last thing, I would like to write a query to compute the week numbers of a data field (Week of the month depending on the day). I posted this here: https://www.accessforums.net/showthr...rom-Date-Value and got a link to http://www.vbaexpress.com/kb/getarticle.php?kb_id=1085 but, again, I have not succeeded in adapting the suggestion given into my DB, please assist. Lets say, we use DateTimeLaid as an example?
Thanks.

[June7]

I should have remembered when I posted in that other thread - the intrinsic DatePart function will return the week of the month or year.
Examples - allowing defaults of Sunday as first day of week and Jan1 as first day of year:
DatePart("w", [DateTimeLaid])
DatePart("ww", [DateTimeLaid])