Access VBA Loading table by Using Append and Update query of data from 2 other tables | Newbie | | Join Date: Oct 2009
Posts: 5
| | |
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!
| |
best answer - posted by 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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
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
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
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?
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
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
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
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;
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
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 : - 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.
- 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.
- The names of the fields too seem obviously to be transcribed sloppily. Why, for instance, use [Customers] to describe the customer name?
| | Newbie | | Join Date: Oct 2009
Posts: 5
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
Quote:
Originally Posted by 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
ADezii, Thanks so much man! It works beautifully. I will take care of Normalization later, thanks again. God bless!
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
Quote:
Originally Posted by JoeKid09 ADezii, Thanks so much man! It works beautifully. I will take care of Normalization later, thanks again. God bless! You are quite welcome, but you should use NeoPa's approach instead of mine.
| | Newbie | | Join Date: Oct 2009
Posts: 5
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
Hey NeoPa thank so much for your help as well. I hope all is well, God Bless!
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Access VBA Loading table by Using Append and Update query of data from 2 other tables
All is well thank you Joe :)
I hope the same is true for you.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,272 network members.
|
