If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Compare data in two records

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,125
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!
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,125
You're welcome!
__________________
Have a nice day!
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #6 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,125
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!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,125
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!
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #11 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,125
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
Compare data in two records-screenshot_qry_tblmaster_j_right-join_tbl_tally.jpg  
__________________
Have a nice day!
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #13 (permalink)  
Old
Moderator
 
Join Date: Mar 2009
Posts: 5,125
You're welcome!
__________________
Have a nice day!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On