I have a list of company (CoID) ratings (Rating) that were applied on a certain date (RatingDate). The rating dates are sporadic, and I am looking to fill in the blanks by assigning the given rating to a specific date from a list of dates, where, if the rating changes on a given RatingDate, the new rating is carried forward on the dates after the change. Currently, my code seems to only carry forward the oldest rating, and doesn’t change if the rating changes on a certain date.
Thank you for any insights,
Public Function PopulateTableOfRatingHistory() Dim dbs As DAO.Database Set dbs = CurrentDb Dim dtDate As Date 'snapshot date Dim sqlAppend As String Dim sqlQueryLastRating As String Dim qdf As DAO.QueryDef Dim rs1 As DAO.Recordset Set rs1 = dbs.OpenRecordset("DATES") 'check this to make sure it imports the table values DoCmd.SetWarnings False rs1.MoveFirst Do While Not rs1.EOF 'get the date value to use as a parameter dtDate = rs1.Fields(1).Value ' get the date value to lookup 'use the date parameter to run the SQL for the last rating as of the given date and append the query result to a table DoCmd.RunSQL "INSERT INTO tblCoDtRtgs (ISIN, CoID, SnapDate, Rating, RatingDate ) SELECT RatingsBackDated.ISIN, RatingsBackDated.CoID, #" & dtDate & "#, Last(RatingsBackDated.Rating) AS LastOfRating, Last(RatingsBackDated.Date) AS LastOfDate " & _ "FROM RatingsBackDated " & _ "WHERE (((RatingsBackDated.Date)<= #" & dtDate & "#)) " & _ "GROUP BY RatingsBackDated.name, RatingsBackDated.CoID, RatingsBackDated.ISIN;" rs1.MoveNext Loop rs1.Close Set rs1 = Nothing Set dbs = Nothing DoCmd.SetWarnings True End Function