Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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,

  2. #2
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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:

    <pre>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
    </pre>

    Legare Coleman

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Brooklyn, New York, USA
    Posts
    176
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •