Pull 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.

 

[tlbroadbent]

Use a query with a self join of the table. Something like this should work though you may need to adjust to fit your situation.

SELECT c1.Claim, MAX(c1.[trans date]) AS CurrDate, c1.examiner AS CurrExaminer, MAX(c2.[trans date]) AS PrevDate, c2.examiner AS PrevExaminer
FROM Claims c1
JOIN Claims c2
  ON c1.Claim = c2.Claim
WHERE c1.[trans date] > c2.[trans date]
  AND c1.examiner <> c2.examiner
GROUP BY c1.Claim, c1.examiner, c2.examiner

Terry L. Broadbent - DBA

"The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents. (Nathaniel Borenstein)