• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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
0
ExcelKid1081
Asked:
ExcelKid1081
  • 2
  • 2
1 Solution
 
Helen FeddemaCommented:
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

0
 
ExcelKid1081Author Commented:
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
0
 
Helen FeddemaCommented:
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.
0
 
ExcelKid1081Author Commented:
I decided to go a different route with this one. Thanks for your reply though
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now