I have and ODBC mdb with two tables and each table has matching data in key fields:
1. CustomerMaster, its key: CMOurAcctID
2. CustomerProductCrossRef, its key: AccountID
Table 1 has only one record for each key value while table 2 may have as many as 40+ records for the same value (many to one).
Also table one has many records that have no matching value in table 2 but every record in table 2 has a matching value in table 1.
I am trying to read through table 1 and get data from every matching row in table 2 if they exist and will write this data into a new table. If I find no matching rows in table 2 I will still write the data from table 1.
When I made a joined of the tables it limited the data to only those row that found a match.
The following code is what I have as my query statement, which works, but the data in the table 2 does not match when I display it. It seems to be ignoring my WHERE clause and I'm about at the end of my ideas.
Any advice?
1. CustomerMaster, its key: CMOurAcctID
2. CustomerProductCrossRef, its key: AccountID
Table 1 has only one record for each key value while table 2 may have as many as 40+ records for the same value (many to one).
Also table one has many records that have no matching value in table 2 but every record in table 2 has a matching value in table 1.
I am trying to read through table 1 and get data from every matching row in table 2 if they exist and will write this data into a new table. If I find no matching rows in table 2 I will still write the data from table 1.
When I made a joined of the tables it limited the data to only those row that found a match.
The following code is what I have as my query statement, which works, but the data in the table 2 does not match when I display it. It seems to be ignoring my WHERE clause and I'm about at the end of my ideas.
Any advice?
Code:
dbExportDB.Open "MarketingDB"
dbImportDB.Open "CampaignDB"
[red]rsTBL1.Open "SELECT * " & _
"FROM CustomerMaster" _
, dbExportDB, adOpenDynamic, adLockOptimistic[/red]
[green]Do Until rsTBL1.EOF[/green]
Hold(1) = "'" & rsTBL1.Fields(0) & "'"
Hold(2) = "'" & rsTBL1.Fields(19) & "'"
[red]rsTBL2.Open "SELECT * " & _
"FROM CustomerProductCrossRef, CustomerMaster " & _
"WHERE AccountID = CustomerMaster.CMOurAcctID " & _
"ORDER BY ProductGroup ASC " _
, dbExportDB, adOpenDynamic, adLockOptimistic[/red]
[blue]Do Until rsTBL2.EOF[/blue]
Select Case Trim("'" & rsTBL2.Fields(2) & "'")
Case "2ND"
Hold(3) = "'" & rsTBL2.Fields(2) & "'"
Case "3WC"
Hold(4) = "'" & rsTBL2.Fields(2) & "'"
Case Else
End Select
rsTBL2.MoveNext
[blue]Loop[/blue]
rsTBL2.Close
rsTBL1.MoveNext
[green]Loop[/green]
rsTBL1.Close