Select based on first item for each group.

[gpeirce]

What I'm trying to do is so simple but I'm missing something.

I want to get a report to track patient's pain level and list when it's increasing. I want rows where pain level is greater than the first observation.

The table:

PatID Date Pain
1 01/1/01 6
1 02/1/01 4
1 03/1/01 7 *report this line

2 01/1/01 6
2 02/1/01 7 *report this line
2 03/1/01 4
2 04/1/01 8 *report ths line

I want to list ID, date, pain level, first pain level.

Would I:

Select a.PatID, A.Date, A.Pain, First(A.Pain)
from DB1 a
where a.pain >
(Select FIRST(Pain) from DB1 b
where a.PatID = b.PatID
order by b.PatID, B.Date
group by b.PatID)

?


Thank you.

 

[MarkK]

Welcome to the forum.

I would do this in the report, not the query. I would create global variables . . .

Dim m_lastPain As Integer
Dim m_count As Integer

. . . and then handle the Patient header Section_Format event and re-init the count, so it restarts at zero for each patient . . .

Private Sub Header0_Format(Cancel As Integer, FormatCount As Integer)
   m_count = 0
End Sub

. . . and then handle the Detail_Format event . . .

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
[COLOR="Green"]   'here we just hide the whole detail section if the pain is not increasing (or if count is zero)[/COLOR]
   Me.Detail.Visible = m_lastPain < Me.Pain AND Count > 1
   m_count = m_count + 1
   m_lastPain = Me.Pain
End Sub

And now you can drastically simply your SQL, which will run much faster . . .

SELECT PatID, [Date], Pain FROM a

Hope this helps,

 

[plog]

FIRST should removed from Access (along with its bastard brother LAST)--horrible horrible function that operates like no one expects or knows. What you should do is create 2 subqueries.

Sub1 - The first will determine the date of every patients' first visit. For this you would bring in the PatID and then use the MIN function on your Date field (which is a poor name for a field because its a reserved word) call that field FirstVisitDate.

Sub2 - would be based on your table and Sub1 to get the actual value of every patients pain level on that first visit. This would be a straight JOIN between the PatID fields and then the FirstVisitDate of Sub1 and the Date field of your table. It would show only the PatID and the pain level of their fist visit (FirstPain).

MainQuery - this would be based on your table and Sub2 you would JOIN them by FirstPain and Pain and then the PatID fields. You would bring down all the fields from your table and then an additional calculated field:

AboveFirstPain: Iif([pain]>[FirstPain], 1,0)

Then in the criteria below that you put 1, so that it selects only records where the current record's pain is above the FirstPain value.

 

[gpeirce]

Wonderful ideas!! I'm an old (OLD!) mainframe SQL programmer and forgot about saving items from record to record. I'll try those approaches.

Thank you all!

 

[gpeirce]

I did it with the subqueries idea. The 1st subquery points to the pain for the minimum date for each patient. Brilliant!

 

[Galaxiom]

FIRST should removed from Access (along with its bastard brother LAST)--horrible horrible function that operates like no one expects or knows.

First() can be useful in an aggregate query where it is known that all the values in a the field will be the same so you don't care which one is returned.

It is more efficient than putting a GroupBy on the field.