Good afternoon all,
I have a database that keeps track of temperatures for individuals (records for individuals stored in 'tblPatients'). Each individual is assigned an ID ('PatientID'), and there are multiple temperatures stored for each individual (temps stored in 'tblTemps' and linked to 'tblPatients' via 'PatientID'). The temperatures are entered using a sub form on the main form; each temperature has a date field and a time field associated with it (so I know when each temp was taken).
I need to create a query that chronologically checks each temperature for an individual until it finds the first temperature that is at or below 33. Then, starting with that temperature value and still working chronologically, do two things: (1) count the total number of temps collected from then on for that individual ["TotalTemps"], and (2) within the number of temps collected in #1, count how many are outside of the range 32.8-33.2 ["OutsideTemps"].
Example:
PatientID Temp TempDate TempTime 1 33.6 3/1/2013 17:30 1 33.1 3/1/2013 18:00 1 32.1 3/1/2013 18:30 1 32.9 3/1/2013 19:00 1 33.1 3/1/2013 19:30 2 33.1 3/2/2013 05:00 2 33.0 3/2/2013 05:30 2 32.6 3/2/2013 06:30 2 32.7 3/2/2013 07:00
In this example, PatientID 1 will return '3' for TotalTemps (starting with the value '32.1') and '1' for OutsideTemps (again, starting with the value '32.1' only 1 value is outside the range of 32.8-33.2). PatientID 2 will return '3' for TotalTemps and '2' for OutsideTemps.
Any help with this issue would be greatly appreciated!