Query to determine differences between sequential data 3

[TheName]

Well, had a consultant run away and drop the huge project he couldn't handle in my lap. I'm thus forced to figure out and implement a single query before a go-live tomorrow morning. Any SQL references I've been able to peruse haven't put me on the right track... So here's my problem:

I've got a table like the following:

Event_Location Event_ID Claim_ID
1073 90100 10114
1073 90101 10114
1075 90102 10114
1073 90103 10114
1023 10201 10226
1032 10202 10226
1034 10203 10226
1055 10204 10226

I need a query which will add a code ("A01", "A02", or "A03&quot to an empty field based on an event's position within each claim. "A01" will specify the first event in the claim (Event_IDs 90100 and 10201 here), "A02" will show any changes of location within the claim (Event_IDs 90102, 10202, and 10203), and "A03" the final event (90103 and 10204).

Now, getting the "A01" and "A03" in was easy enough. I just cannot, however, figure out a way to add the "A02"s. While I could easily pull it off in Excel, the sheer volume (38,000 claims comes out to a staggering number of records) is prohibitive.

Can anyone help me out here? I could really use it!

Thanks,
Ben Scott

 

[JerryDennison]

Since eventID is a sequential number you could create a query that has a column that adds one to this number. Then use this query along with the original table(or query) you're using linked via event_location, event_id, and claim_id. This is called recursive joins, where you basically join a table with itself. Only in this case we've modified it by altering the actual join the following record with the previous record. You would set the join type between all fields to include all records from the main table(or query) and only those matching from this query. This will give you a null value for the first record in every grouping. You will then be able to test for changes from the previous records value directly on that record's row.

 

[TheName]

Thanks Jerry! Though my example was populated with idealized data and the actual Event_IDs are not sequential, recursive linking was useful. Unfortunately, it didn't get me too far; I can pull the distinctive locations but, again, can't compare them to their surrounding records.

I appreciate the advice!
Ben Scott

 

[TheName]

Addendum: I should add that I've now pulled records where the Event_Locations did not match while the Claim_IDs did but end up with extra records. For example, using the data posted previously I come up with:
[tt]
Claim_ID a.Event_Loc b.Event_Loc a.Event_ID b.Event_ID
10114 1073 1075 90100 90102
10114 1073 1075 90101 90102
10114 1073 1075 90103 90102
10114 1075 1073 90102 90100
10114 1075 1073 90102 90101
10114 1075 1073 90102 90103
[/tt]etc.

Any suggestions?
Ben Scott

 

[JerryDennison]

Are you saying that you have some multiple records where the Claim_ID, Event_Loc, and Event_ID are the same? Then my suggestion won't work this scenario. You've piqued my interest. Can you send me a db with the pertinent tables and enough records to see what you're faced with? Also, a detailed description of what you're trying to do(I think I fully understand, but now am confused somewhat since the data is not what I originally thought)?

jerrydennison@telocity.com

 

[TheName]

Jerry,

I e-mailed the sample database to you and the only query I've been able to come up with to pull records with different Event_Location. I can send you the queries to update the first and last events if you'd like them for reference purposes. Thanks!

Ben Scott

 

[JerryDennison]

I've e-mailed back the modified database with the recursive queries I was talking about.

 

[JerryDennison]

Ben,

I think I found a solution! It involved using the recursive queries I originally developed AND some creative coding. Basically, I took the final query that got us 99% of the way there and made it a make table query. I then wrote code to iterate through this temporary table and update the intermediate patient moves. I sent this final db back to you.

WHEW! This was a tough one (at least for me).