Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    Compare data in two records

    Using VBA is there a way that I can compare a data field in the current record to a data field in the next record.

    I have already linked to the data table and can view and go through the table but I have not been able to compare the tow records.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,254
    Here's an example:
    Code:
    Function Compare2Rows()
    
        Dim rst1 As DAO.Recordset
        Dim rst2 As DAO.Recordset
        
        '
        ' Open the first recordset.
        '
        Set rst1 = CurrentDb.OpenRecordset("My_Table", dbOpenSnapshot)
        '
        ' Open the second recordset using the Clone method of the first one.
        '
        Set rst2 = rst1.Clone
        '
        ' Move rst1 pointer to the first of the rows to compare.
        '
        rst1.FindFirst ("RowId = 122")
        '
        ' Now set the current record of rst2 = current record of rst1.
        ' (assigning the bookmark of a recordset to another one is only
        ' possible because the second recordset was obtained using the
        ' Clone method of the first one. In such a case both recordsets
        ' share the same bookmarks).
        '
        rst2.Bookmark = rst1.Bookmark
        '
        ' Move rst2 to the next record.
        '
        rst2.MoveNext
        '
        ' Now the current record of rst1 is Row(n) while
        ' the current record of rst2 is Row(n+1).
        ' We can perform the comparison(s) between both
        ' sets of fields.
        '
        ' ...
        '
        ' Clean up when done.
        '
        rst2.Close
        rst1.Close
        Set rst1 = Nothing
        Set rst2 = Nothing
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    I'll give it a try

    Thanks I will give it a try; once I get a chance I will post what I currently have if for some reason I cannot get it to work right.

    Thanks again.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,254
    You're welcome!
    Have a nice day!

  5. #5
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    not working right

    I was able to use the code but when I tried to get the comparision to work, this is giving me a headache. Here is what I am trying to do.

    I have an access database that collect data from another source; each record has a 4 fields:

    TStamp - its value is a number that keeps all messages in order, it is not a real time

    MsgType a text field
    UserID a text field
    RcvID a text field

    What I am trying to do is have the code go through the table and add lists where there is a break in time.

    A start table looks like this
    TStamp MsgType UserID RcvID
    1 V2 K21 d55
    1 V2 K21 d55
    3 V2 K21 d55
    4 V2 K21 d55
    5 V2 K21 d55
    9 V2 K21 d55

    Output should be this
    TStamp MsgType UserID RcvID
    1 V2 K21 d55
    1 None KA 00 <added line from code >
    2 V2 K21 d55
    3 V2 K21 d55
    4 V2 K21 d55
    5 V2 K21 d55
    6 None KA 00 <added line from code >
    7 None KA 00 <added line from code >
    8 None KA 00 <added line from code >
    9 V2 K21 d55

    My current code works if the table it is going through does not have duplicate numbers in TStamp. Most tables I have to review have multiple numbers that are the same in TSamp (1,1,1,1,1,2,2,3,3,3,4,4,4,4, 7,7,7,8,8,8, 10)

    I am new at this and cannot figure out how to make it skip values that repeat. Here is the current code.

    Any idea on how to do this?

    Thanks

  6. #6
    Join Date
    Mar 2009
    Posts
    5,254
    I don't have all the elements I need to understand what you try to do. I can imagine that you need to complete the sequence of TStamp values by inserting "missing" rows in the gap between two non-adjacent values of TStamp (e.g. add rows with TStamp values of 6, 7 and 8 because there are no such rows in the original table) however I don't understand why you replace the values of the second row having a TStamp value of 1:
    Code:
    TStamp MsgType UserID RcvID
    1 V2 K21 d55
    1 None KA 00 <added line from code > Original line in start table was: 1 V2 K21 d55
    2 V2 K21 d55
    3 V2 K21 d55
    4 V2 K21 d55
    5 V2 K21 d55
    6 None KA 00 <added line from code > TStamp 6 was missing in start table
    7 None KA 00 <added line from code > TStamp 7 was missing in start table
    8 None KA 00 <added line from code > TStamp 7 was missing in start table
    9 V2 K21 d55
    I'm not even sure that it is the second line with a TStamp value of 1 that was skipped and not the first one as all values are the same inside a column for every columns except for TStamp.

    Moreover you wrote "Here is the current code" but you did not include any in your message.

    If you need to create a recordset where only one row for each TStamp value is present, here is a solution:
    Code:
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT StartTable.TStamp, " & _
                        "First(StartTable.MsgType) AS MsgType, " & _
                        "First(StartTable.UserID) AS UserID, " & _
                        "First(StartTable.RcvID) AS RcvID " & _
                   "FROM StartTable " & _
               "GROUP BY StartTable.TStamp " & _
               "ORDER BY StartTable.TStamp;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
    Have a nice day!

  7. #7
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    Added Code

    I have tried a defferent route but keep getting an error. I have attached the code I keep getting an error in the Loop once it enters it at the rstNew.AddNew

    Code:
    Function Compare2Rows()
    
    Dim rst1 As DAO.Recordset
    Dim rst2 As DAO.Recordset
    Dim rstNew As Recordset
    Dim strTableName As DAO.Database
    
    
    
    
    ' sets up loop and time variables
    
    Dim strSQL As String
    Dim datStart As Double
    Dim datEnd As Double
    Dim datTime As Double
    Dim DT As Double
    
    
     
        datStart = DFirst("MinOfCollectms", "MJ_Min_Max")
        datEnd = DFirst("MaxOfCollectms", "MJ_Min_Max")
        datTime = datStart
        
        
        'Name if table that you wish to modify
    
        Set strTableName = CurrentDb
    
    
        '
        ' Open the first Recordset
        '
        
        Set rstCurrentData = CurrentDb.OpenRecordset("tblMaster_J", dbOpenSnapshot)
        
        '
        ' Open the recordset using the Clone Method  of the first one.
        '
        
        Set rstCurrentData2 = rstCurrentData.Clone
        '
        ' Move rst1 pointer to the of the rows to campare
        '
        
        rstCurrentData.FindFirst (RowID = 1)
    
        '
        ' Now set the current record of rst2 = current record of rst1.
        ' (assigning the bookmark of a recordset to another one is only
        ' possible because the second recordset was obtained using the
        ' Clone method of the first one. In such a case both recordsets
        ' share the same bookmarks).
        
        Do While datEnd > rstCurrentData!Collectms
        
        rstCurrentData2.Bookmark = rstCurrentData.Bookmark
        
        '
        ' Move rst2 to the next record.
        '
        
        rstCurrentData2.MoveNext
        
        '
        ' Now the current record of rst1 is Row(n) while
        ' the current record of rst2 is Row(n+1).
        ' We can perform the comparison(s) between both
        ' sets of fields.
        '
               
            ' If you have run out of current data to look at then just add the new record
            
            If Not rstCurrentData.EOF Then
            
            If rstCurrentData!Collectms<= rstCurrentData2!Collectms Then
            
                    If datTime < rstCurrentData2!Collectms Then
                  
                        With rstNew
                            rstNew.AddNew
                            rstNew!Collectms = DT + rstCurrentData!Collectms
                            'rstNew!Collectms = datTime
                            rstNew!MsgType = mtype
                            rstNew!SenderID = sid
                            rstNew!WordID = vid
                            rstNew.Update
                        End With
                    End If
                    rstCurrentData.MoveNext
                End If
            End If
              
                DT = 1
                mtype = "None"
                sid = "00"
                vid = "A"
        Loop
    
        '
        ' Clean up when done.
        '
    
        rstCurrentData2.Close
        rstCurrentData.Close
        Set rstCurrentData = Nothing
        Set rstCurrentData2 = Nothing
    
    
    End Function
    Last edited by desertwrangler; 07-27-11 at 15:03. Reason: typo

  8. #8
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14
    I do appoligize for not being clear; i am pulling logs from another database. It is a record of messages set between systems. Messages are sent at the same time then you get times where nothing happens. I am added those lines with the values you see so that I can plot times where nothing happens.

    Does that clear it up a little bit.

    Thanks

  9. #9
    Join Date
    Mar 2009
    Posts
    5,254
    You don't explain what the error is (error code + message). I can see several issues with your approach but I'd like to know which one you're facing.
    Have a nice day!

  10. #10
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    reply

    As I stated the Error occurs once it passes to the inner loop at the AddNew and it is:
    Runtime Erro 91, Object Variable not set.

    I am sure there is a better way to get this done but every way I have tried I run into an error. The previous way I was doning it it just could not handle data with like values in the Collectms column.

    Thanks

  11. #11
    Join Date
    Mar 2009
    Posts
    5,254
    That should not be the only error:

    1. rstCurrentData is not declared anywhere.

    2. rstCurrentData2 is not declared anywhere.

    Note: If you do not have the line:
    Code:
    Option Explicit
    in the Declarations section of a module, all variables that are not explicitly declared (i.e. using a Dim instruction) are of type Variant, except if you use Deftype instructions.

    3. In:
    Code:
        rstCurrentData.FindFirst (RowID = 1)
    the criteria should be a litteral (i.e. "RowID = 1") but it makes no sense since there is no field named RowID into the recordset (in a previous post you wrote that the columns of the table were: TStamp, MsgType, UserID and RcvID). If you want to move to the first record, use:
    Code:
    rstCurrentData.MoveFirst
    4. mtype, sid, vid are not declared anywhere (see above):
    Code:
                            rstNew!MsgType = mtype
                            rstNew!SenderID = sid
                            rstNew!WordID = vid
    and:
    Code:
                mtype = "None"
                sid = "00"
                vid = "A"
    5. In lines such as:
    Code:
            If rstCurrentData!Collectms <= rstCurrentData2!Collectms Then
    where does Collectms come from? (in a previous post you wrote that the columns of the table were: TStamp, MsgType, UserID and RcvID.)

    6. Though declared:
    Code:
    Dim rstNew As Recordset
    rstNew is never initialized before you use it in the line:
    Code:
                        With rstNew
    At that stage in the code rstNew is nothing (i.e. rstNew Is Nothing = True) as there is no previous line to open the RecordSet, such as:
    Code:
    rstNew = CurrentDb.OpenRecordset(...
    This is the meaning of the error you describe. Moreover the type of RecordSet (ADO or DAO) is not specified: this can be a problem (not always) as the default object type can vary according to the version of Access and/or to the ranking order of the references in your project.

    7. I'm not sure of what the lines:
    Code:
        datStart = DFirst("MinOfCollectms", "MJ_Min_Max")
        datEnd = DFirst("MaxOfCollectms", "MJ_Min_Max")
    actually mean, partly because I don't know what MJ_Min_Max is (a query, I guess, but with which SQL statement?). However I'm rather sure that using the values datStart and datEnd together with datTime to control the iterations of the loop is not wise nor logical or safe. Not wise because you don't now in advance what values they can have. Not logical because when you have to process a recordset from the first to the last line, the normal (and logical) method consists in using an instruction such as:
    Code:
    Do Until RecordSet.EOF
    This at least garantees that the loop will never be executed if the recordset is empty (i.e. both .BOF and .EOF are True). Moreover the whole recordset will always be processed. Accessorily the logic behind the code is easier to understand. Not safe because you cannot assert that the loop will not run past the last record of the recordset. This is because you control the iterations of the loop with values extracted from the data set processed inside it, while you never test for .EOF = True.

    8. An other issue should arise because you use:
    Code:
    rstCurrentData2.MoveNext
    then test the value of one of its fields:
    Code:
                    If datTime < rstCurrentData2!Collectms Then
    while rstCurrentData2.EOF will be True (i.e. "No Current Record") before the end of rstCurrentData as rstCurrentData2 is always one record ahead.

    IMHO it would be more efficient to use one or possibly two queries to perform this kind of operation. Consider the following SQL statement:
    Code:
    SELECT Tbl_Tally.SysCounter
         , tblMaster_J.TStamp
         , tblMaster_J.MsgType
         , tblMaster_J.UserID
         , tblMaster_J.RcvID
      FROM tblMaster_J RIGHT JOIN Tbl_Tally 
        ON tblMaster_J.TStamp = Tbl_Tally.SysCounter
    WHERE (Tbl_Tally.SysCounter <= (SELECT MAX(TStamp) FROM tblMaster_J));
    Where Tbl_Tally is a tally table (i.e. a table with a single column (here named SysCounter) containing adjacent increasing integer values: 1, 2, 3... etc. This query yields a result such as the one you can see in the attached screenshot and does most of the work. Using it into a RecordSet loop, or better in a second UPDATE query (if possible) would complete the columns from tblMaster_J where the values are Null. Removing the duplicate values from the data set would not be difficult either.
    Attached Thumbnails Attached Thumbnails ScreenShot_Qry_tblMaster_J_RIGHT JOIN_Tbl_Tally.jpg  
    Have a nice day!

  12. #12
    Join Date
    Jan 2004
    Location
    Fort Stewart, GA
    Posts
    14

    Thanks for the help

    I have made I think most if not all of the changes that you suggested and it is working a lot better. I cannot do the query that you suggested because the TStamp is always different and some of the data tables that we pull are 10000 records or more thats why I am trying to do the injecting of records this way.

    I have taken the min and max lines out, they pulled data from a query that gave me a start and stopping point

    My only issue now is getting the correct logic to use in the the loop.

    Thanks for your help; I truly appreciated it. A lot of the mistakes in not declaring variables happened in the changing completely the code I used and of course I did overlook it.

    Thanks again.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,254
    You're welcome!
    Have a nice day!

Posting Permissions

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