Cycle Through two Tables

  • Thread starter Thread starter Dominic
  • Start date Start date
D

Dominic

Hi

I have two tables with similar data in them and i want to
be able to build a list by doing the following

from table1 find records that are in table2 and add them
to the table1 and then do the cycle again until it doesn't
find any more records related in table2

Does anyone know the best way to do this

Thanks in advance
 
You can do it with a single Append query. The general technique is:

INSERT INTO Table1
SELECT Table2.*
FROM Table2 LEFT JOIN Table1
ON Table2.Key = Table1.Key
WHERE Table1.Key IS NULL

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
hi thanks for the reply but i don't think i explained
myself properly

i have two tables

in table1 i have 1 field
in table two i have 2 fields

i join table1 to field1 on table2
this is a one to many join
Table2 field2 contains data that can be used as the key in
table1 if it finds data i want to add field 2 as the key
and carry on cycling records untill no more occurances can
be found:

table1 table2 field1 field2
jon jon tom
fred jon rob
mark mark richard
sally richard kate
billy emma

the result i want is for table 1 to end up looking like
this

jon
fred
mark
sally
tom
rob
richard
kate

note that kate was added because it was tagged to richard
and richard was added previously

hope you can help

Regards
Dom
 
Oh. Run this until it finds no more records to insert:

INSERT INTO Table1
SELECT Field2 FROM Table2
WHERE Table2.Field1 IN
(SELECT Field1 FROM Table1)
AND Table2.Field2 NOT IN
(SELECT Field1 FROM Table1)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thanks that works fine i just need to get the system to
run it until it finds no records now!

Cheers

Dom
 
You can do it from code like this:

Dim db As DAO.Database

Set db = CurrentDb
Do
db.Execute "qryInsertThatJohnGaveMe", dbFailOnError
Loop Until db.RecordsAffected = 0

Where qryInsertThatJohnGaveMe is the name of the query containing the SQL
that I gave you below.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top