asked on

DAO Recordset Calculate Difference between Columns in Two Tables

Hello-
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.

Thanks!
EK

Helen Feddema

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
      rst1.MoveNext
   Loop
   
End Sub

Open in new window

ExcelKid1081

ASKER

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!

Thanks!
EK

ASKER CERTIFIED SOLUTION

Helen Feddema

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ExcelKid1081

ASKER

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