appending new Records only

[murlen]

I have three tables, table 1 'ServerTest' contains 5 records which should not change, table 2 'Region' has 40+ records.
using an append quary all records are combined into table 3 'Junction1'

my problem...
the Region table will change over time and new records will be added.
how do I append the new records only to the junction1 table?

this is my append quary;
INSERT INTO Junction1 ( RegionID, TestID, TestNumber, TestDescription )
SELECT Region.RegionID, ServerTest.TestID, ServerTest.TestNumber, ServerTest.TestDescription
FROM Region, ServerTest;


thanks
Murlen

 

[Jon K]

Try this:-

INSERT INTO Junction1 ( RegionID, TestID, TestNumber, TestDescription )
SELECT [Region].[RegionID], [ServerTest].[TestID], [ServerTest].[TestNumber], [ServerTest].[TestDescription]
FROM Region, ServerTest
WHERE Region.RegionID not in (Select RegionID from Junction1);

It will insert only the new RegionIDs.

Note
Using NOT IN is inefficient if the tables Region and Junction1 are large.
.

 

[murlen]

that works

Thanks for the help!



... this place is great!

 

[accessman2]

Does it work in MS Access' query?

Because I try to use similar style in the query of the MS Access, it give out the error message "Query is too complex".

thanks.