Finding Second Occurrance of String Using Query

[EBox]

Hello:

I am a novice Access user, and am using a dataset which represents "event log" activity on an electronic medical record system for a hospital. Each record in the dataset has a unique patient identifier, event time stamp, and indicates the field name that is being changed, the old value and new value for that field.

For the "status" field, there are multiple values I am tracking (HFLR, HICU, HMON) - all begin with "H".

Sometimes the same value comes up multiple times in a patient stay. For example, their "status" field is "HFLR", then switches later to "ADMI" and then may go back to "HFLR" following that.

I would like to calculate the total time that the patient is in an "H*" status during their stay (i.e. time the patient is placed into a "H*" status until removed from that status). Not sure how to do this, and would welcome any assistance that can be provided.

Many thanks in advance,
EBox

What I would like to do is be able to calculate the time between all "H*" events

 

[kjv1611]

Are you asking about how to setup a query to do this?

If so, then you might could setup a crosstab query that totals up for each patient how much time he or she spent in each status. Then, you could just take the data for the specific status or statuses you are looking for from that.

To do so, you could start with hitting the "new" button at the top of the database window while in the Queries section, and begin simple with it. Then, you can edit it in design view, later, and add additional criteria and such.

I would think that would be somewhat of a start.

To learn about the crosstab queries, you can do a search in the Microsoft Access Help File. There are certainly plenty of resources online as well.