Counting values after a specific value

[cardgage]

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!

[burrina]

I am not 100% sure of my syntax but maybe someone here can check and elaborate! Something along the lines of: Hey Paul, where you at? Chime in on this!
Good Luck With Your Project. The below code would go in your query grid.

PatientID = [Enter a Patients Name]
TempDate Ascending

<= "33"

DCount("*","[YourTableName]","[TotalTemps] = " & "33")
DCount("*","[YourTableName]","[TotalTemps] = " & "<>33")

[cardgage]

I'm not exactly sure where you intend for me to put each of these in my query...could you please elaborate?

[burrina]

If you are wanting to enter a patients name via the query, then you would enter the syntax under the field Patient Name. If your wanting the count, then enter it like OutsideTemps: DCount("*","[YourTableName]","[TotalTemps] = " & "33")

This is just an example, like I said, maybe someone else can chime in here! Explain it better or offer a better solution. A screenshot of the query would help.

[cardgage]

Well, I figured my problem out by exporting my query into Excel and using nested if statements within Excel (example data attached). Here were my steps:

(1) Created an Access query called qryTemps: Pulled in PatientID (sort ascending), TempDate (sort ascending), TempTime (sort ascending), Temp
(2) Exported query into Excel spreadsheet
(3) Created column "CountPerPt" that counts the total number of Temps per PatientID
(4) Created column "CountTot" that counts the total number of Temps for each PatientID after (and including) the earliest temperature <33
(5) Created column "CountOut" the counts the total number of Temps within the Temps included in CountTot that are outside the range of 32.8-33.2
(6) Created column "Ratio" that calculates the percentage of valid Temps outside of the pre-specified range for each PatientID (this was what I wanted Access to calculate)

It would be still be nice to try to figure out a way for Access to do this to avoid the steps of exporting the query to a spreadsheet every time I want this calculated. The rules that burrina posted didn't quite fit the rules that I needed for my query, and I couldn't quite figure out how to utilize the DCount function to get Access to spit out the data that I wanted. I do appreciate the assistance, though!

[burrina]

Glad you got it figured out. Good luck with your project!