DAO Recordset Calculate Difference between Columns in Two Tables

Posted on 2009-12-17
Last Modified: 2013-11-27
I have a question regarding how to loop through one recordset and find the difference between a second recordset. What I have is two tables, with a one-to-many relationship. Both Tables have the exact same structure (ie. field names, # fields). I would like to loop through each record in the Many table and calculate the difference for for each field between the two tables. This could be done in a Select Query however, I have +30 fields and I am thinking there must be a way to do this programatically.

Question by:ExcelKid1081
    LVL 31

    Expert Comment

    That is a very strange setup (one-many related tables with identical field structure).  I will assume that there is a good reason for it.  You can easily set up two recordsets, one based on the "one" table and the other on the "many" table.  Iterate through the first, then do a FindFirst on the second to get a match on the relevant field, and calculate the difference (however this would be done -- you don't give enough details for me to write precise code for this).  Here is some code for iterating through one DAO recordset and finding a match in another:
    Public Sub IterateRecordset()
       Dim rst1 As DAO.Recordset
       Dim rst2 As DAO.Recordset
       Dim lngID As Long
       Dim strSearch As String
       Set rst1 = CurrentDb.OpenRecordset("tblEmployees", dbOpenDynaset)
       Set rst2 = CurrentDb.OpenRecordset("tblContacts", dbOpenDynaset)
       Do While Not rst1.EOF
          Debug.Print "On ID: " & rst1![EmployeeID]
          lngID = rst1![EmployeeID]
          Debug.Print "Current ID: " & lngID
          'Try to find matching ID in other table
          strSearch = "[ContactID] = " & lngID
          rst2.FindFirst strSearch
          If rst2.NoMatch = False Then
             Debug.Print "found matching ID"
             'Your code here
          End If
    End Sub

    Open in new window


    Author Comment

    I see where you are going with this code but its not quite what I am looking for. Perhaps my explanation of my data was a little off so here is another shot:

    I have two tables with same field number of fields. What I would like to do is subtract the value for each field between them. For example, TableOne.Field(1)-TableTwo.Field(1).

    However, since there are numerous records in each table I would like to be able to set the filter on the recordset for each so that the appropriate records are being selected (I already have code for this).

    What I really need help with is how do I set up a loop so that after my filters are applied I can get the difference between TableOne.Field(1)-TableTwo.Field(1), TableOne.Field(2)-TableTwo.Field(2), TableOne.Field(3)-TableTwo.Field(3) and so on...? Hope that makes sense!

    LVL 31

    Accepted Solution

    How are you matching up the appropriate records in the two tables?  You say they are related one-to-many, so presumably there would be multiple matches in the "many" table for each record in the "one" table, on the linking field.  If you do have code that matches appropriately, then for each record, just do the subtraction on each field in turn.  You could set up a loop to work with Fields 1 - the highest number, or just reference each field separately.  For creating the names dynamically, you need to work with the DAO Field object, and its Name property.

    Author Closing Comment

    I decided to go a different route with this one. Thanks for your reply though

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now