Is this possible? How clients move from procedure to procedure across time analysis.

[joe789]

Hi Folks,

I am struggling with trying to create a query. I have a normalized base table that contains a unique identifier for each person, a date of service, and a unique procedure rendered on that date of service. I have the data sorted by unique identifier, date of service, and procedure rendered on that day of service. There are only four possible procedures that a person can receive. I am attempting to create some sort of flow-chart (for lack of a better work), or a history of where a client starts at and what happens thru time. For example:

Client: Date: Procedure:

123 7/1/09 Lavage
123 8/1/09 Lavage
123 9/1/09 Extraction
123 10/1/09 Lavage
123 11/1/09 Removal


So I would get something similar to the following results created a timeline of change:

Client 123 received Lavage than Extraction then Lavage then Removal.


Is there a way to do this with a query or set of queries. The difficulty here is that some of these procedures are the same ones back to back, I am attempting to show essentially when the procedures change with time.

Thank you for your help.

Joe

 

[jdraw]

But you have the output

123 7/1/09 Lavage
123 8/1/09 Lavage
123 9/1/09 Extraction
123 10/1/09 Lavage
123 11/1/09 Removal

that shows the history of procedures for client 123?

What else were you trying to show? You want it all on one line?

 

[joe789]

I am trying to display something like

123 Lavage
123 Extraction
123 Lavage
123 Removal


from

123 7/1/09 Lavage
123 8/1/09 Lavage
123 9/1/09 Extraction
123 10/1/09 Lavage
123 11/1/09 Removal

the procedure lavage, for example, occurs back to back so I would only show that procedure once ... problem is some of these procedures occur every single day and so in order to only show the flow of change thru time, I would only want to show a procedure one time unless it changes then show the change ...

Thanks