Calculated field based on the value of the next record

[benignor]

Hi,
I have a query that contains the results of repeated tests on a number of persons. The goal of treatment is to get those test results below 50. I need to find, for each person, the period of time (with a start and an end date) during which their test was below 50. The dataset looks like:

ID + LabID + LabDate + Result + Undet
----+------+-----------+-------+------
101 + 21 + 1/1/2000 + 780 + 0
101 + 21 + 2/2/2000 + 320 + 0
101 + 21 + 3/3/2000 + 50 + 1
101 + 21 + 4/4/2000 + 50 + 1
102 + 21 + 6/6/2005 + 1000 + 0
102 + 21 + 7/7/2005 + 900 + 0
102 + 21 + 8/8/2005 + 50 + 1
103 + 21 + 9/9/2005 + 50 + 1
103 + 22 + 9/30/2005 + 567 + 0

What I need is a query that produces the following:

ID + StartDate + EndDate
----+-----------+---------
101 + 3/3/2000 + 4/4/2000
102 + 8/8/2005 + 9/9/2005

I can also provide an attachment with more data, if that would help. Thanks in advance,

 

[Premy]

Your example is kind of confusing. Does a test complete on the same day or does it take several days to complete? I mean if I do a test on day 1 and it was, let's say 45, then another on day 5 with a score of 60, then another on day 9 with a score of 48 then I have:
Test1 Day1 = 45
Test2 Day5 = 60
Test3 Day9 = 48

How would I put a time period for a <50 score without overlapping test2?

Regards,
Premy

 

[The_Doc_Man]

OK, here is the bad news/good news.

There IS no next record.

Datasets are not necessarily ordered. Unless, of course, you supply the order. But there is no query that will give you the next record because the word "NEXT" is not fully operational in a data set. The original design concept behind SQL was that it would APPEAR as though everything happened to the recordset in parallel, a bit like a black box operation. So in queries, you cannot do "NEXT"

HOWEVER...

If the records have dates, you can impose order. So it would be possible for you to write criteria in which you would use "Result < 50" as one of the criteria and would make an aggregate query out of it, by finding the MIN and MAX dates meeting that criterion.

But the part that is MUCH harder...

Suppose in your case where you show ID 101 that there were TWO disjoint cases where this student scored below 50 - but with an intervening higher score.

The upshot of this that you cannot really do this easily with a query. VBA might be the way to go. You can impose "memory" in VBA code that is not supported by SQL. And that is what you need in the general case here.

 

[benignor]

Yes, this is part of the complexity. A test is done, and result obtained, on the same day. The test is never repeated on the same day. But the test is repeated multiple times on the same person, and it can be "undetectable" (meaning below 50) at any/some of those times, all of them, or none of them. If it is undetectable some of those times, then the result should produce multiple records per person, each with a start and a stop date that encompasses each of the periods during which the person was undetectable.

Is this more clear? Thanks for your wlingness to help.

 

[benignor]

Doc Man:

Indeed, I was hoping that the date (which as far as Access is concerned, is a sequential number anyway) could be used to identify the "next" record. And yes, you are exactly right, if a value above 50 occurs in the middle of two "runs" of values below 50, then the desired result is 2 rows containing the two intervals befor and after the value above 50.

I really wouldn't know where to even beging with VBA, so if there is any way that this can be accomplished through queries, this wold be much appreciated!

Best, and thanks for reading this post.

 

[WayneRyan]

benignor,

I really hate to say it, but this is one of the few times that I'd reccommend a
temporary table. I don't see any way that a query can handle this, even with
calling a VBA function.

For each ID
 +->Find the minimum <= 50 (from the start --> initially 1/1/1900)
 |   If None, then done, go to next ID
 |   If found, then is there a > 50 value at a later date?
 |      If none enter ID, StartDate, Now()
 |      If later, enter ID, StartDate, "That LabDate"
 +---Set the StartDate to "That LabDate" look for another segment


Dim rstIn As DAO.RecordSet

Dim StartDate As Date
Dim EndDate As Date
Dim TestDate As Variant

Dim Finished As Boolean

Set rstIn = CurrentDB.OpenRecordset("Select Distinct(ID) From YourTable Order By ID")
While Not rstIn.EOF And Not rstIn.BOF
   StartDate = #1/1/1900#
   Finished  = False
   While Not Finished
      '
      TestDate = DMin("[LabDate]", _
                      "YourTable", _
                      "[ID] = " & ID & " And [Result] <= 50 And LabDate > #" & StartDate & "#")
      If IsNull(TestDate) Then
         Finished = True
      Else
         StartDate = CDate(TestDate)
         TestDate = DMin("[LabDate]", _
                         "YourTable", _
                         "[ID] = " & ID & " And [Result] > 50 And LabDate > #" & StartDate & "#")
         If IsNull(TestDate) Then
            EndDate = Now()
            DoCmd.RunSQL "Insert Into Temp (ID, StartDate, EndDate) " & _
                         "Values (" & ID & ", #" & StartDate & "#, #" & EndDate & ");"
            Finished = True
         Else
            EndDate = CDate(TestDate)
            DoCmd.RunSQL "Insert Into Temp (ID, StartDate, EndDate) " & _
                         "Values (" & ID & ", #" & StartDate & "#, #" & EndDate & ");"
            StartDate = EndDate
         End If
      End If
      Wend
   rstIn.MoveNext
   Wend
End Sub

hth,
Wayne

 

[Premy]

I think this query might get you what you want or almost.

SELECT tblReadings.ReadID, tblReadings.PersonID, tblReadings.LabDate As sDate, (SELECT TOP 1 T2.LabDate FROM tblReadings AS T2 WHERE T2.PersonID = tblReadings.PersonID AND T2.LabDate > tblReadings.LabDate ORDER BY T2.LabDate, T2.ReadID ) AS eDate, tblReadings.Result
FROM tblReadings
WHERE tblReadings.PersonID=101 And tblReadings.Result<50;

The PersonID parameter is hardcoded here for testing purpose, so for the real thing u should pick up the parameter from your form or something.

Attached a little sample for easy testing

Well Wayne, This is not to challenge your point of view, it's just that I've been fiddling around with subqueries lately and thought I could solve the problem at hand. But I'm not 100% sure it will work exactly as needed as I did not test it extensively.

Regards,
Premy

 

[benignor]

Many, many thanks, guys. Will probably not be able to get to this until tonight, but will test it and report back.

 

[WayneRyan]

Premy,

Not to worry. The concept of any single person having potentially several
"windows" where they are undetectable just led to an iterative approach.

I very, very rarely use temporary tables, but just didn't see any way
around it for this.

Hopefully, Benignor will inform us as to what works.

See ya,
Wayne

 

[benignor]

Premy:

Almost indeed! I did not test it with live data yet - I used your test file instead, and it works beautifully! But then I added another test below 50 on 1/4 (between the 1/1 and 1/5 dates, both of which are below 50). This generated an extra row in the query results that now started on 1/1 and ended on 1/4.

Ideally, the desired result is a single row that goes from 1/1 to 1/5, i.e., I would like to have rows that capture each period of uninterrupted results below 50. This is why I titled this thread as I did, because it seems that somehow the query would have to "check" the next test in order to decide whether to "stop" at the current record and return its date as a stop date, or keep going to the next record.

I am enclosing your modified test file. Hope this is clearer. Thank you so much for your help.

 

[Premy]

Well, that'll take some extra steps then, like a MakeTable Query and some code. In the attached sample there's now a form, just open it and select a name to see what it does. Then take a look behind the scenes to see the how. The query from the previous example has been enhanced a little and now also fills in the current date if there's no end date yet for a test.

Let me know and good luck

Premy

 

[benignor]

Premy:

This looks like you had to do a lot of work. Thank you very much for taking the time. We are going to look at this in detail with my colleagues here, and we'll let you know how it went.

 

[Premy]

No prob. U see, when my brain gets close to melting point while working on my projects I need to divert it to some specific and delimited problem (like yours for example) for it to cool down a bit. It's just healthy recreation ;-).

Regards,
Premy