Compare to Previous Record

[hlock]

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

claim trans date examiner
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
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. Any suggestions? I would appreciate
it. Thanks.

 

[Allen Browne]

Use a subquery to get the examiner for the previous date.

This kind of thing:

SELECT Table1.*
FROM Table1
WHERE Table1.examiner <>
(SELECT TOP 1 examiner
FROM Table1 AS Dupe
WHERE (Dupe.Claim = Table1.Claim)
AND (Dupe.[trans date] < Table1.[trans date])
ORDER BY Dupe.[trans date]);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

(Note that this solution assumes that no claim appears twice on the same
date. If it does, the concept of 'previous' claim is undefined.)

 

[hlock]

I will try this. However, the claim number can be repeated on the same day.
How would I handle that? Thanks!

Use a subquery to get the examiner for the previous date.

This kind of thing:

SELECT Table1.*
FROM Table1
WHERE Table1.examiner <>
(SELECT TOP 1 examiner
FROM Table1 AS Dupe
WHERE (Dupe.Claim = Table1.Claim)
AND (Dupe.[trans date] < Table1.[trans date])
ORDER BY Dupe.[trans date]);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

(Note that this solution assumes that no claim appears twice on the same
date. If it does, the concept of 'previous' claim is undefined.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

claim trans date examiner
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
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. Any suggestions? I would
appreciate
it. Thanks.

 

[Allen Browne]

You must provide some way for Access to determine which is the 'previous'
record for a claim.

If your table has an AutoNumber primary key, you might decide that the
higher primary key number is the more recent one. In that case, you could
add the primary key to the ORDER BY clause in the subquery, e.g.:
ORDER BY Dupe.[trans date], Dupe.[ID]);

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I will try this. However, the claim number can be repeated on the same
day.
How would I handle that? Thanks!

Use a subquery to get the examiner for the previous date.

This kind of thing:

SELECT Table1.*
FROM Table1
WHERE Table1.examiner <>
(SELECT TOP 1 examiner
FROM Table1 AS Dupe
WHERE (Dupe.Claim = Table1.Claim)
AND (Dupe.[trans date] < Table1.[trans date])
ORDER BY Dupe.[trans date]);

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

(Note that this solution assumes that no claim appears twice on the same
date. If it does, the concept of 'previous' claim is undefined.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

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

claim trans date examiner
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
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. Any suggestions? I would
appreciate
it. Thanks.