I'm using Access 97 to store inform

[ainkca]

I'm using Access 97 to store information downloaded from another application.

I've got a table of patient results for restraint usage. 'Initiated' is a result, and 'Removed' is a result. What I need to do is figure out how long a patient was in restraints. Since it's possible staff could chart either one, or both, does anyone have any idea how I might attempt this calculation? Result Date/Time are stored together in a single field, but there's a separate row for each.

How will I match up which 'removed' goes with which 'initiated'? if it's possible one of them is missing? Some sort of probablity logic? I've never done anything like that. It's possible for a single patient to have multiple restraint initiations in the same 'visit', espcially in for example, mental health.

For example, I could have records that look like this:

PatientNameOne Initiated 3/11/03 4:15 PM
PatientNameOne Removed 3/11/03 8:00 PM
PatientNameOne Removed 3/13/03 4:00 PM
PatientNameTwo Initiated 3/12/03 9:15 AM
PatientNameTwo Removed 3/12/03 11:00 AM

The second 'Removed' for PatientNameOne didn't have an Initiated charted. In a perfect world they will, but we're talking about human data entry here, and it DOES happen.

Any ideas? Unfortunately, there's no sort of indicator to tell which Removed belogs with which initiated.

How could I compare the date/time field to another record using some sort of if logic to determine the likelihood 'this' one matches 'that' one. Any suggestions welcome!

Thanks,
ainkca

 

[dhookom]

You could try a subquery (caution air sql):
SELECT Patient, Result As RemoveTime,
(SELECT TOP 1 ResultTime
FROM tblA A
WHERE A.Patient = tblA.Patient
AND A.ResultTime < tblA.ResultTime
ORDER BY A.ResultTime Desc) as InitiateTime
FROM tblA
WHERE Result = &quot;Removed&quot;;

Duane
MS Access MVP

 

[ainkca]

Thanks for the suggestion, here's what I tried:

SELECT qryResults.Acct, qryResults.PName, qryResults.ResultDtTm, qryResults.ResultName, qryResults.LabelName, qryResults.Annotation, (SELECT TOP 1 A.ResultDtTm
FROM qryResults A
WHERE qryResults.Acct = A.Acct
AND A.ResultDtTm < qryResults.ResultDtTm
ORDER BY A.ResultDtTm DESC) As InitiateTime
FROM qryResults
WHERE (((qryResults.ResultName)=&quot;Removed&quot);

I got this error:
&quot;At most one record can be returned by this subquery.&quot; I had to click OK to the same error three times, then it gave me a single record with #Name? in all the fields.

Obviously I've done SOMETHING wrong... can you point me in the right direction?

 

[dhookom]

Try:
SELECT qryResults.Acct, qryResults.PName, qryResults.ResultDtTm, qryResults.ResultName, qryResults.LabelName, qryResults.Annotation,
(SELECT TOP 1 A.ResultDtTm
FROM qryResults A
WHERE qryResults.Acct = A.Acct
AND A.ResultDtTm < qryResults.ResultDtTm
AND A.ResultName =&quot;Initiated&quot;
ORDER BY A.ResultDtTm DESC) As InitiateTime
FROM qryResults
WHERE (((qryResults.ResultName)=&quot;Removed&quot);

If you still get the error, you may need to add a unique key field to the ORDER BY
ORDER BY A.ResultDtTm DESC, PK) As InitiateTime



Duane
MS Access MVP

 

[ainkca]

Okay, I tried that, and I still got the same error. How do I add the unique key? I know PK in your example is supposed to be the field, but am I using something like the Account#?(that didn't work, I didn't think it would, but in deperation we try ridiculous things).

Please forgive me for being dumb today. How do I create a unique key?

Thanks for helping me.
ainkca

 

[dhookom]

The issue seems to be that you have duplicate values in your query. IOW you have records that have the same Acct, ResultDtTm, and ResultName. Do you have any field in qryResults that is unique for every record?

Duane
MS Access MVP

 

[ainkca]

Hi,

Actually, Acct#, ResultDtTm, and ResultName combined are all part of the key for the table so that duplicate rows don't populate when staff import the data. So there aren't duplicates.
The problem is just what you say though... there's not a way for me to distinguish that a matches b, because the acct number is the same... so I either get nothing, or I get a cartesian product.
What about if I made the acct = acct in the subquery, but made the resultdate etc NOT equal? Do you think that would work? I'll try it anyway...

 

[ainkca]

I tried it, and it still doesn't work, but I only get the error twice now instead of three times?! Go figure. I'm going to keep playing.

 

[dhookom]

The subquery does have acct=acct. I am a little confused because you have use Acct, Acct#, and Account#. Are these all the same field? Could you paste your current SQL into a message?

Duane
MS Access MVP

 

[ainkca]

Sorry, yes they are the same field, I'm just not being consistent (shame on me).
The manager who wanted it came by my desk today though, and I recommended we change our charting application to collect the information better. I'm going to try and make that happen first, and she agreed. I think even if I got it to work, it would only ever be 'logically' making the connection. It will be much better if we can design it so that we know for sure.

Thanks SOOOO much for your help, I greatly appreciated it.

ainkca