Access VBA Loading table by Using Append and Update query of data from 2 other tables

[JoeKid09]

Hey guys!

Thanks in advance for your help. I'm working in loading a table with values from a product table, and then update those products with every customer name, So that every single customer name will be associated with all products. Note: there is not relationship on these tables yet. Below are the table’s contents

PRODUCT TABLE
Prod_id Product
1 orange
2 apple
3 pineapple
4 grape
5 carrots

CUSTOMER TABLE
Cust_ID Customers
1 Peter Smith
2 John Hopkins
3 Carl Fisherman
4 Josehp Perez
5 Kathy Baez
6 Sofia Scarlett

TRANS_CUSTOMER TABLE

Cust_ID Cust_Name Product
1 Peter Smith orange
1 Peter Smith apple
1 Peter Smith pineapple
1 Peter Smith grape
1 Peter Smith carrots
2 John Hopkins orange
2 John Hopkins apple
2 John Hopkins pineapple
2 John Hopkins grape
2 John Hopkins carrots
orange
apple
pineapple
grape
carrots

for customer number 3 will be update into the trans_cust table. Right now I'm doing this one by one, is there any way I can do a loop so I can update every single customer record into the trans_cust table. thanks so much!

[ADezii]

Here is one Method you can use that will populate the TRANS_CUSTOMER TABLE with every combination of Customer and Product. I'm totally ignoring the fact that the Database needs some Normalization and Re-Structuring:

Dim MyDB As DAO.Database
Dim rstCustomer As DAO.Recordset
Dim rstProduct As DAO.Recordset
Dim rstTransCustomer As DAO.Recordset
 
Set MyDB = CurrentDb
Set rstCustomer = MyDB.OpenRecordset("CUSTOMER TABLE", dbOpenForwardOnly)
Set rstProduct = MyDB.OpenRecordset("PRODUCT TABLE", dbOpenSnapshot)
Set rstTransCustomer = MyDB.OpenRecordset("TRANS_CUSTOMER TABLE", dbOpenDynaset)
 
Do While Not rstCustomer.EOF
  Do While Not rstProduct.EOF
    With rstTransCustomer
      .AddNew
        ![Cust_id] = rstCustomer![Cust_id]
        ![Cust_Name] = rstCustomer![Customers]
        ![Product] = rstProduct![Product]
      .Update
    End With
      rstProduct.MoveNext
  Loop
    rstProduct.MoveFirst
    rstCustomer.MoveNext
Loop
 
rstTransCustomer.Close
rstProduct.Close
rstCustomer.Close
Set rstProduct = Nothing
Set rstCustomer = Nothing
Set rstTransCustomer = Nothing

[NeoPa]

Are you looking for a way to add records into the [TRANS_CUSTOMER] table such that every customer has a record that matches with every product? A Cartesian Product in fact?

[NeoPa]

If that's what you're looking for (both ADezii & I seem to read it that way), then this could hardly be easier as the two tables need simply be included without any type of JOIN in the query :

INSERT INTO [TRANS_CUSTOMER]
            (Cust_ID,Customer,Prod_ID,Product)
SELECT      tC.Cust_ID,
            tC.Customer,
            tP.Prod_ID,
            tP.Product
FROM        [CUSTOMER] AS tC,
            [PRODUCT] AS tP

[ADezii]

NeoPa's solution is cleaner, more efficient, and right on point. I simply adjusted the SQL to conform with your Object Names and requirements (hope you didn't mind NeoPa). The SQL Statement has been tested and is fully operational.

INSERT INTO [TRANS_CUSTOMER TABLE] ( Cust_ID, Cust_Name, Product )
SELECT tC.Cust_ID, tC.Customers, tP.Product
FROM [CUSTOMER TABLE] AS tC, [PRODUCT TABLE] AS tP;

[NeoPa]

I think the three differences are down to confusion ADezii. The OP was not very well considered and I'm sure included some sloppy mistakes. Putting two & two together :

1. The table names don't have the " TABLE" in them if I read the post correctly. It would be highly unlikely for a name to have an underline AND a space separating words.
2. The OP forgot to include the Prod_ID value in his posted data. Clearly it makes little sense to have the description on its own, otherwise they would follow the same logic for the customer.
3. The names of the fields too seem obviously to be transcribed sloppily. Why, for instance, use [Customers] to describe the customer name?

[JoeKid09]

@ADezii


ADezii, Thanks so much man! It works beautifully. I will take care of Normalization later, thanks again. God bless!

[ADezii]

@JoeKid09
You are quite welcome, but you should use NeoPa's approach instead of mine.

[JoeKid09]

Hey NeoPa thank so much for your help as well. I hope all is well, God Bless!

[NeoPa]

All is well thank you Joe :)

I hope the same is true for you.