Add Records from a Recordset to another Recordser if not exist

jsdba

Registered User.
Local time
Today, 07:54
Joined
Jun 25, 2014
Messages
165
Table1
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Tomato|
Veg | Lettuce|
Grain | Bread |
Dairy | Milk |

Table2
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Lettuce|
Grain | Bread |

How do i add records from Table1 to Table2 if the record does not already exist in table 2 using VBA
 
Usually this is done with a unmatched query that is converted into an append query. If you unload a database with these tables with some data I'll give you a concrete example. I will need to know which fields are the primary keys.
 
SQL = "INSERT INTO Table2 ( food, item ) "
SQL = SQL & "SELECT Table1.food, Table1.item "
SQL = SQL & "FROM Table1 LEFT JOIN Table2 ON Table1.food = Table2.food AND Table1.item = Table2.item "
SQL = SQL & "WHERE Table2.food Is Null"
CurrentDb.Execute SQL
 
Table1
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Tomato|
Veg | Lettuce|
Grain | Bread |
Dairy | Milk |

Table2
------------------
Food |Item |
-----------------|
Fruits | Apple |
Veg | Lettuce|
Grain | Bread |

How do i add records from Table1 to Table2 if the record does not already exist in table 2 using VBA
Why do you want two table with the same data, it is poor database concept?
 
Why do you want two table with the same data, it is poor database concept?

Right. You can accomplish that with one table with Food, Item adding a column called Group. Then test if food+item you are testing exist in Group 2, and the combo does not exist you add it.

Code:
If DCount("*", "Foodtable", "Food = '" & myfood & '" AND Item = '" & mytitem & "' AND Group = 2") = 0 Then 
   CurrentDB.Execute "INSERT INTO Foodtable (Food, Item, Group) VALUES ('myfood', 'myitem', 2)"
End if

Best,
Jiri
 
Right. You can accomplish that with one table with Food, Item adding a column called Group. Then test if food+item you are testing exist in Group 2, and the combo does not exist you add it.
But still two tables with the same data, why?
 
But still two tables with the same data, why?

JHB The business logic is very complex. The actual table structure is nothing like what i posted. I just wanted to see a how i can accomplish this using vba/sql.
 
SQL = "INSERT INTO Table2 ( food, item ) "
SQL = SQL & "SELECT Table1.food, Table1.item "
SQL = SQL & "FROM Table1 LEFT JOIN Table2 ON Table1.food = Table2.food AND Table1.item = Table2.item "
SQL = SQL & "WHERE Table2.food Is Null"
CurrentDb.Execute SQL

Worked thanks
 

Users who are viewing this thread

Back
Top Bottom