Tracking Changes in Records in Large Database (XP and 2000)

Topic

New Reply

Here’s the scenario: I have a large database (up to 10,000 records) in which companies are identified by certain levels (among other things). The data in the database are updated frequently by multiple users. Over the course of time, a company may move from one level to another. My task is to track the changes. So, for example, company XYZ might be classified as level E in January. In February it might be classified as level D. In May it might move to level B. What II need to do is to produce a report on a regular basis (monthly, say) where I can identify all the companies who changed levels during the previous time period and what the changes were (e.g., Company XYZ went from D to C, Company STU went from B to D, etc.).

Added info: the database actually consists of perhaps 15 fields (maybe more in the final version), and I will be producing pivot tables to illustrate other facets of the data. I already know how to get the results I want from the pivot tables, but I am not sure how to even begin to track the records over time to reflect the level changes.

I will be grateful (as always) for any suggestions, ideas, etc.

Thanks in advance,

Reply | Quote

Replies

The first thing that came to my mind was simply taking a “snapshot” of the database each month. With the database open you would click File…Save As…then choose a location on your computer and save it as, say Jan’03. Next month do the same thing for Feb. On the Feb report you would add a column and lookup January level for each customer. Then it’s just a matter of comparing the two columns and sorting them by the ones that have changed.
Hope this is of some help.
Stats

Reply | Quote

That sounds like an approach that will work, at least in the beginning. Thanks for the suggestion.

Reply | Quote

How you would do this would depend on how far back you need to be able to get changes. If you you only care about the previous month, and never want to get the month before last, and you are only concerned about the last change made to a company (in other words, if a company changes from E to D and then to C in the same month you only need to see the change from D to C not E to C), then you could use a simple Worksheet Change event VBA routine to save the previous value in another column. Then at the end of the month you could would just report all companies with an entry in this column and then clear the column.

If you need to track all changes, then another method would be to again use the Worksheet Change event routine to log the change date, company name, old level, and new level on a change log sheet. You could then report what you needed from that sheet. If the company names are in column A, and the Levels are in column B, then the code to do this would look something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oLvl As Range, oCell As Range
Dim lCnt As Long, I As Long, lLastRow As Long
        Dim vNewVal() As Variant, vOldVal() As Variant
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Set oLvl = Intersect(Target, Range("B:B"))
    lCnt = oLvl.Cells.Count
    ReDim vNewVal(1 To lCnt) As Variant, vOldVal(1 To lCnt) As Variant
    I = 1
    For Each oCell In oLvl
        vNewVal(I) = oCell.Value
        I = I + 1
    Next oCell
    Application.Undo
    I = 1
    For Each oCell In oLvl
        vOldVal(I) = oCell.Value
        oCell.Value = vNewVal(I)
        I = I + 1
    Next oCell
    With Worksheets("LevelLog").Range("A1")
        lLastRow = .Offset(Worksheets("LevelLog").UsedRange.Row + _
          Worksheets("LevelLog").UsedRange.Rows.Count, 0).End(xlUp).Row
        I = 1
        For Each oCell In oLvl
            If vOldVal(I)  vNewVal(I) Then
                .Offset(lLastRow, 0).Value = Now()
                .Offset(lLastRow, 1).Value = oCell.Offset(0, -1).Value
                .Offset(lLastRow, 2).Value = vOldVal(I)
                .Offset(lLastRow, 3).Value = vNewVal(I)
                lLastRow = lLastRow + 1
            End If
            I = I + 1
        Next oCell
    End With
    Application.EnableEvents = True
End Sub

Reply | Quote

Legare, thank you for your suggestion and the code. I need to spend some time with your code to see if it gets at my problem. I will let you know.

Thanks again.

Reply | Quote