Is this query even possible with Access???

[imtheodore]

I have patients with temperature readings every minute. Some last an hour some last multiple hours, some last days. There could be 100, 500, or thousands of values.
I need to find the 10th value and 10 values before the end of the value list.
So if someone had 60 temperature readings, I need the 10th and the 50th.
Is this possible or do I need more tools?
If so do you have any examples?

 

[KKilfoil]

Assuming you have a proper date/time field to timestamp your reading called [ReadingTime], you can do this with 4 queries:

SELECT TOP 10 {FIELDS} FROM {TABLE} ORDER BY [ReadingTime]

gets the 10 'smallest' or oldest readings.

SELECT TOP 10 {FIELDS} FROM {TABLE} ORDER BY [ReadingTime] DESC

gets the 10 'largest' or newest readings.

Then just find the last and first of these records the same way, to isolate your 10th oldest and 10th youngest records.

 

[imtheodore]

Ok, I sort of understand, but that gives me the top 10 for the enitre table. I need the top 10 for each person in the table. Each person may have 100 to 1000 readings....

 

[tehNellie]

SELECT TOP 10 {FIELDS} FROM {TABLE} WHERE [PATIENTNAME] = 'name of patient' ORDER BY [ReadingTime]

On the basis that the number of patients in your table presumably isn't static you could use a bit of VBA to create a union query of the "top ten" readings for each patient in the table.

So your "Show top ten" button would
1) query the table in the first instance to SELECT DISTINCT each patient name.
2) Use that recordset to generate a Select query similar to the above for each patient name and join them together in a UNION query
3) Finally, open the Union query to display the results.

 

[KKilfoil]

I can think of a code-free 'brute force' way to do this for all patients, but you are going to need some 'cascading' queries!

1) Create a summary query that finds the newest (max) reading and oldest (min) reading, grouped by patient, that shows the [readingtime].

2) Create a query that returns all records, excluding the 2 records per patient with the particular [readingtime] found in (1):
In the [readingtime] criteria space, put in something like not like Query1.[MaxofReadingTime] and not like Query1.[MinofReadingTime]

3) Use the results of (2) to repeat step (1), pruning off the 'max' and 'min' record from that that recordset, etc. Repeat this 10 times!

It is not pretty, but it saves coding.

 

[ByteMyzer]

Assume the following:

Table: MyTable
--------------
Patient (Text)
Temperature (Number)
DateTime (Date/Time)

Here is the SQL:

SELECT M1.*
FROM MyTable AS M1
WHERE M1.DateTime=
(
 SELECT MAX(M2.DateTime)
 FROM MyTable AS M2
 WHERE M2.Patient=M1.Patient
 AND M2.DateTime IN
 (SELECT TOP 10 M3.DateTime
  FROM MyTable AS M3
  WHERE M3.Patient=M1.Patient
  ORDER BY M3.DateTime
 )
)
OR M1.DateTime=
(
 SELECT MIN(M4.DateTime)
 FROM MyTable AS M4
 WHERE M4.Patient=M1.Patient
 AND M4.DateTime IN
 (SELECT TOP 11 M5.DateTime
  FROM MyTable AS M5
  WHERE M5.Patient=M1.Patient
  ORDER BY M5.DateTime DESC
 )
)

 

[imtheodore]

Thanks Guys!! I'll give these a try today and see what I can come up with...

Dave

 

[imtheodore]

This query always causes my PC to hang, Do you know what is wrong with it?

Assume the following:

Table: MyTable
--------------
Patient (Text)
Temperature (Number)
DateTime (Date/Time)

Here is the SQL:

SELECT M1.*
FROM MyTable AS M1
WHERE M1.DateTime=
(
 SELECT MAX(M2.DateTime)
 FROM MyTable AS M2
 WHERE M2.Patient=M1.Patient
 AND M2.DateTime IN
 (SELECT TOP 10 M3.DateTime
  FROM MyTable AS M3
  WHERE M3.Patient=M1.Patient
  ORDER BY M3.DateTime
 )
)
OR M1.DateTime=
(
 SELECT MIN(M4.DateTime)
 FROM MyTable AS M4
 WHERE M4.Patient=M1.Patient
 AND M4.DateTime IN
 (SELECT TOP 11 M5.DateTime
  FROM MyTable AS M5
  WHERE M5.Patient=M1.Patient
  ORDER BY M5.DateTime DESC
 )
)

 

[ByteMyzer]

Make sure that the Patient field is indexed (duplicates allowed) in the table, and try it again. How many records are you querying against in total?

 

[imtheodore]

Make sure that the Patient field is indexed (duplicates allowed) in the table, and try it again. How many records are you querying against in total?

I just indexed the field, same result, the PC hangs.
I am only querying 810 records, I'm using a sample of the real Data (300,000 records) Originally I thought the data set was too large, but it hangs with only 810 records. The smaller I make the data set, the quicker it hangs.
I even renamed my table "MyTable" and the filed "Patient" so I know I have no typos.

 

[ByteMyzer]

I just tested it on my system, with 2000 records, and the query did not hang, so I don't know what to tell you.

 

[imtheodore]

I just tested it on my system, with 2000 records, and the query did not hang, so I don't know what to tell you.

Ok, I took it out of Access and ran it from SQL (Toad) and it ran fine. In fact it's incredible that there was a simple solution to what I thought was a complex problem. Thanks!
Now I have to figure out how to get the difference in the temps, it will be tough since the values are in different rows. I may be posting another question shortly

Thanks again!

Dave