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.