(Solved) – Trouble Assigning a rating to a specific date based on a rating on a separate date

  • by

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

    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;"


    Set rs1 = Nothing
    Set dbs = Nothing

  DoCmd.SetWarnings True

  End Function

Leave a Reply

Your email address will not be published. Required fields are marked *