SQL Query statement in VB 2

[ranshe]

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?

    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

 

[HarleyQuinn]

What are you trying to achieve??

From what I can see Hold(1) and Hold(2) will change everytime the outer loop is executed but that Hold(3) and Hold(4) will always be the last values from the inner recordset.

Is this the case???

If that's answered a bit confusingly please post back and I will try and clarify what I mean.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[AndyLee100]

Shouldnt it be a left join if you want to return all rows from table1 if there is not match in table2?

What happens when you run your query from Access? Does this give you what you want. If not try changing the join type to left join not inner join.

Andy

 

[PeteJohnston]

I think I would go along with Andy. If you do something like

SELECT CustomerMaster.*, CustomerProductCrossRef.*
FROM CustomerMaster 
LEFT JOIN CustomerProductCrossRef 
ON CustomerMaster.CMOurAcctID = CustomerProductCrossRef.AccountID

will this give you a single recordset with all of the records from CustomerMaster and CustomerProductCrossRef? If there are no matching CustomerProductCrossRef records it should return Null values in the fields from the CustomerProductCrossRef table.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[HarleyQuinn]

Agreed with both the above posts it would be much easier to do it that way. The code you were working with (with the two recordsets) had the capacity to go very badly wrong and was going wrong (as you stated). I was trying to point out problems with the code you currently had.

Cheers

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[ranshe]

Thanks!
Super help!
Worked perfectly....GREAT!!!!!
Thanks again!