By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,483 Members | 1,544 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,483 IT Pros & Developers. It's quick & easy.

Value from previous record

P: 1
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.
Dec 12 '08 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
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
Dec 13 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.