Access New Record Creation using Query 2007

I am trying to create an append query that w开发者_如何转开发ill take new data entered into one table merge it with a common set of records from a second table and then add it to a thord table. the new data entered can have the same fields but in different combinations, currently the query i have continually updates all records in the first table thus creating duplicate records in the third table.

query as is below

INSERT INTO ANSWERS ( [CUST KEY], [BU KEY], [QUESTION NO], QUESTION )
SELECT [CUSTOMER INPUT].[CUST KEY], [CUSTOMER INPUT].[BU KEY], QUESTIONS.[QUESTION ID], QUESTIONS.QUESTION
FROM [CUSTOMER INPUT], QUESTIONS;

That should be something like :

INSERT INTO Answers ( 
    [Cust Key], [Bu Key], 
    [Question No], Question ) 
SELECT 
    [Customer Input].[Cust Key], 
    [Customer Input].[Bu Key], 
    Questions.[Question Id], Questions.Question 
FROM [Customer Input]
INNER JOIN Questions
ON [Customer Input].[Question Id] = Questions.[Question Id]
WHERE [Customer Input].[Cust Key]
NOT IN (SELECT [Cust Key] FROM Answers)

I have guessed at a common field for Questions and Customer Input, hence :

[Customer Input].[Question Id] = Questions.[Question Id]

As an aside, do yourself a favour and get rid of all spaces in field and table names before life gets too complicated. You can use an underscore or just delete them.

You may wish to read
Fundamental Microsoft Jet SQL for Access 2000
Intermediate Microsoft Jet SQL for Access 2000
Advanced Microsoft Jet SQL for Access 2000