I have a table set up as follows

ID    Event     Date
1        20        5/01
1        21        5/12
1        30        7/11
2        99        2/6
3        99        3/4
3        20        4/18
4        21        6/21
4        20        7/1
4        30        7/30

The events represent admission (99), hospitalization (20), return from the
hospital (21) and discharge (30).  What I need to do is to calculate the
interval between hospitalization and return from the hospital.  In all
instances a return date is later than the hospitalization date but some
clients who are hospitalized are discharged prior to returning (handling
this exception is not a problem for me).

One way to do this is to un-normalize the data such that events are arrayed
across columns of the same record but I have to believe there is a more
elegant solution.  I can probably handle the coding once I figure out how to
make row to row comparisons.  It seems to me that multiple rows of data for
each client ID have to somehow get read into an array but I don't know how
to do this.  Can anyone help?

Eric Graig

One way, is to first use a query to filter only the client ID at hand.  Then
do a find on the recordset to locate the desired event code.  If it's found,
then you can store that value in a variable for later use in calculation,
which by the way, can easliy be done using the DateDiff function.
Obvioulsy, you'd have to trap for cases like, not fiding the desired event
code, or duplicate event codes for the same client (multiple visits?)

I'm not sure what you're doing really requires the use of an array.  After
all, you can do the same type things with a recordset.

HTH
C YA