Assigning value in Field "A" Record "X" to Field "B" Record "Y"

[DonKHotay]

Assigning value in Field "A" Record "X" to Field "B" Record "Y"

Hi there,
I hope the title does justice in explaining what I am trying to do. I have a table that contains the fields policynum, transreason, policyeffdate, and transdate. "Transreason" indicates whether or not there was a transaction on a policy (policynum). If there was a transaction then a "transdate" is available. A policy can have multiple transactions and transaction dates. Ultimately what I want to do is find the number of days between each transaction. For the first transaction, I want the days inbetween the policyeffdate and the transdate, for the second transdate the days inbetween the first transdate and second transdate, etc....

Any suggestions? I'm trying to do this with a module function similar to a counting function. It's tricky with the dates though, especially since some policies have just 1 transdate and others have more than 1. Here's some sample data:

PolicyNum PolicyEffDate TransDate TransReason
10029 8/1/2004 1/3/2005 EUH
10029 8/1/2004 5/25/2005 EUI
10053 1/31/2004 6/23/2004 EUI
10053 1/31/2004 12/31/2004 ELUH
10065 3/1/2004 3/10/2004 EVUI
10162 3/15/2004 8/17/2004 ELUH
10162 3/15/2004 8/31/2004 EUI
10166 5/27/2004 10/15/2004 UI

 

[gromit]

Hi -

Here's an idea, maybe this works or not -

For each policy, sort the transaction dates. Then match each date with the next greatest date (this can be done in a query). The duration will be the difference between these matched pairs. (The last date will not have a matched pair, but that should be okay).

There may be some complications but take a look at the attached and see if this merits any further looks.

hth,

- g

 

[DonKHotay]

Hey thanks alot, that does indeed work! However it has led to another problem. I'm working with nearly 80,000 rows of data so it's very difficult to run the query and impossible to table the data, the query just hangs up when I try. And I'm only trying to do the NextDate step. Any ideas on how I can make it run faster with that much data and be able to table it?

 

[gromit]

Sorry, didn't realize that you had so much data to manipulate. DLOOKUP is sort of convenient for small uses, but it is not particularly fast.

I would try the approach in VBA. I think with the data sorted correctly (first by policy, then by transdate), you could make a single pass through the data and just use a "look ahead approach". I.e. buffer the current record and test whether the next record is the same policy. If so, then match the dates. Otherwise, flush the buffer and move on.

- g

 

[DonKHotay]

Nice, perhaps you can give me some hints to code it properly?


Dim WPolicyNum As Long
Dim WTransDate As Date

Function GetNextDate(PolicyNum) As Date
If PolicyNum = WPolicyNum Then
?? WTransDate = Transdate ??
GetTransDate = WTransDate
Else
WTransDate = TransDate
GetNextDate = Null
WPolicyNum = PolicyNum
End If

End Function

 

[gromit]

Try the attached. You can open the form frmCalculate and hit the button. It should write the records to the temporary table tblzDurations.

Look at the code and see if you have any questions. You do need to include the DAO object library in your references for this to work.

I have included a message box that pops up every 1000 records to ask if you want to continue. This is just to give you an out if things go haywire.

Not sure how fast this would work on a large record set like you describe. If you try it, please post back and let me know how it goes.

- g

 

[DonKHotay]

G, thank you thus far you have really taught me a bunch. The code is providing for me tremendous insight and understanding into VB, which I am eager to learn.

It's not without a hitch though. I get a "Run-time error '3421': Data type conversion error." after the first 528 records. Any ideas? When debugging at that point I show !PolicyNum as NULL and rsSource!PolicyNum as 32811.

 

[gromit]

Sounds like you are missing a policy number there. I did not include much in the way of error handling. No wait, I didn't include ANY error handling.

You may want to run a query on the original data to check for missing values, values outside reasonable limits, etc.

- g

 

[DonKHotay]

No nulls and no values outside of reasonable limits. I reviewed the source table at the record where the run time error pops up and there is nothing out of the ordinary there. I went ahead and deleted a few records from the table beginning at 529 (which is when the run time error pops up) and no change. It does not seem to be able to handle more than 528 records.

 

[DonKHotay]

It seems that I needed to change the PolicyNum field in tblzDuration from Integer to Long, additionally the counter had to be set to Long as well. This works very well, thanks again.

 

[gromit]

You're welcome! Glad that it worked.

 

[DonKHotay]

Gromit, perhaps you can lend your expertise again? I need to incorporate a cancellation date into this duration function. Currently the final enddate is the final TransDate (sorted ascending of course). How can I get the final enddate to be the CXDate. Note that CXDate is always greater than TransDate, if a policy is not cancelled and remains effective for it's 1 year life then CXDate is currently assigned a date value of 1/1/1001. I plan on changing this to CXDate+364 (+365 if Leap).

Is it reasonable to split the writing to the Destination table (tblDurationz) into two parts (when there is a cancellation date and when there is not)?

Or can it be done in one fell swoop?