Value from previous record

[hlock]

Access 2007 on XP - I have a table that has:

claim trans date examiner
1 1/2/08 MB
1 1/2/08 MB
1 2/9/08 MB
1 5/21/08 DG
1 7/18/08 DG
2 1/5/08 KY
2 3/1/08 KY
2 3/1/08 KY
3 1/6/08 PB
3 3/19/08 PB
3 6/12/08 RS

What I need to do is find out which claims changed examiners during a certain period of time. For example, during the month of June 08, which claim was transferred to another examiner. In the above example, the result would be claim #3. So - I need to compare the claim number in a record to the claim number in the previous record. If it is the same, I need to then
see if the the examiner is the same. If it is, I don't want to include it in my results. If the examiner is not the same, I DO want to include it in my results. If the claim number is not the same, I need to move to the next record and begin the comparison again.

I've even though of ending up with something like this:

claim trans date examiner prevclm prevexmnr
1 1/2/08 MB 0 0
1 1/2/08 MB 1 MB
1 2/9/08 MB 1 MB
1 5/21/08 DG 1 MB
1 7/18/08 DG 1 DG
2 1/5/08 KY 1 DG
2 3/1/08 KY 2 KY
2 3/1/08 KY 2 KY
3 1/6/08 PB 2 KY
3 3/19/08 PB 3 PB
3 6/12/08 RS 3 PB

Then I could pull the record(s) where claim=prevclm and examiner<>prevexmnr during a certain time period. I've spend way to much time on this and I can't get it figured out. I'm not very good on vba, but if someone could help me I can figure it out. Any suggestions? I would appreciate it. Thanks.

[FishVal]

Hello, hlock.

There are two options - VBA and SQL (as always because they are two :D ):

• SQL self-join on the same [claim no] and [trans date] = <the maximal [trans date] less then that compared to and [claim no] is the same>. This could be done in several ways. The most optimal I see so far is:
  • join the table with itself on the same [claim no] and different [examiner] - this gives all possible transfer variants; from those we need only sequential transfers
  • now the records with the same [trans date] and least ([trans date] - [prev trans date]) are considered to be sequential - a simple grouping query will return what you need
  • now filter the obtaibed dataset by required date interval
• VBA function opening the table and searching for "previous record". When applied in query, it will return the data set similar to that you've provided in the example.

Regards,
Fish