Consolidating data and joining tables based on dates

[ChrisAlc]

I have 2 tables which I need to join. One holds account data based on policy number which is updated each month when a policy makes a payment the other has the policy history.

POLICYHISTORY

strPolicyNumber strCoverChoice datUpdateTimeSt amp
336392BRPA P 01/08/2009
336392BRPA PX 27/05/2010
336392BRPA P5 12/07/2010

ACCOUNTS

Policy_Id MonthEndDate PremiumPaid
336392BRPA 31/08/2009 £7.98
336392BRPA 31/08/2009 £7.98
336392BRPA 30/09/2009 £7.98
336392BRPA 31/10/2009 £7.98
336392BRPA 30/11/2009 £7.98
336392BRPA 31/12/2009 £7.98
336392BRPA 31/01/2010 £7.98
336392BRPA 28/02/2010 £7.98
336392BRPA 31/03/2010 £7.98
336392BRPA 30/04/2010 £7.98
336392BRPA 31/05/2010 £7.98
336392BRPA 30/06/2010 £7.98
336392BRPA 31/07/2010 £7.98
336392BRPA 31/08/2010 £0.78
336392BRPA 31/08/2010 £0.00
336392BRPA 31/08/2010 £8.76
336392BRPA 10/09/2010 £8.76


I need to add the cover choice from the policy table for each entry in the Accounts table. The problem is that I need to get it to add the cover choice based on the month end date to give something that looks like this:

Policy_Id MonthEndDate PremiumPaid strCoverChoice
336392BRPA 31/08/2009 £7.98 P
336392BRPA 31/08/2009 £7.98 P
336392BRPA 30/09/2009 £7.98 P
336392BRPA 31/10/2009 £7.98 P
336392BRPA 30/11/2009 £7.98 P
336392BRPA 31/12/2009 £7.98 P
336392BRPA 31/01/2010 £7.98 P
336392BRPA 28/02/2010 £7.98 P
336392BRPA 31/03/2010 £7.98 P
336392BRPA 30/04/2010 £7.98 P
336392BRPA 31/05/2010 £7.98 PX
336392BRPA 30/06/2010 £7.98 PX
336392BRPA 31/07/2010 £7.98 P5
336392BRPA 31/08/2010 £0.78 P5
336392BRPA 31/08/2010 £0.00 P5
336392BRPA 31/08/2010 £8.76 P5
336392BRPA 10/09/2010 £8.76 P5


The cover choice changed on the 27/05/2010 and 12/07/2010 so all entries in the accounts table with a month end dates after these dates will have the corresponding cover choice. This is the first time I've used a forum like this so apologies if I've not followed the correct posting convention bu any help would be appreciated as I'm stumped..

[MMcCarthy]

So if I understand your problem correctly you have to match the MonthEndDate to the datUpdateTimeSt amp. So we need to clarify the logic of what you are doing. In your example you have an August datUpdateTimeSt amp of 01/08/2009. So you need to add the Cover Choice to all records in the history table after that date and before the date of the september update? If that is not the correct logic can you tell me what is.

[ChrisAlc]

The policy was first created on the 01/08/2009 with cover choice P. The cover choice then changed to PX on the 27/05/2010 and then changed again to P5 on the 12/07/2010. I need to add the cover choice to each entry in the accounts table based on what teh cover choice was at each month end date. Hope this explains it a bit more clearly.

[MMcCarthy]

OK I'm not sure if I have the logic completely correct so I suggest running this on a copy of your database rather than the original.

This code presumes you have added the strCoverChoice field to table accounts. It's a stand alone function so you can run it from whereever.

Function temp()
Dim rs As DAO.Recordset
Dim strPolicy As String
Dim dateFrom As Date, dateTo As Date

    With CurrentDb
        Set rs = .OpenRecordset("SELECT * FROM POLICYHISTORY ORDER BY strPolicyNumber, datUpdateTimeStamp")
        rs.MoveFirst
        
        Do Until rs.EOF
            strPolicy = rs!strPolicy
            dateFrom = rs!datUpdateTimeStamp
            
            rs.MoveNext
            
            If strPolicy = rs!strPolicy Then
                dateTo = rs!datUpdateTimeStamp
            Else
                dateTo = Now()
            End If
            
            rs.MovePrevious
            
            DoCmd.RunSQL "UPDATE ACCOUNTS SET strCoverChoice='" & rs!strCoverChoice & "' " & _
                 "WHERE PolicyID='" & rs!strPolciyNumber & "' " & _
                 "AND MonthEndDate BETWEEN #" & dateFrom & "# AND #" & dateTo & "#"
                 
            rs.MoveNext
        Loop
    
End Function