Access New Record Creation using Query 2007

Question

I am trying to create an append query that will 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;

If the tables are not actually related (i.e., the records in one are not a child related the other), you may find the Cartesian product (which is what you have) useful. If it is what you need, but is producing duplicates, you could try SELECT DISTINCT instead. — David-W-Fenton

Fionnuala Fionnuala · Accepted Answer · 2010-12-31T10:30:01

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

Access New Record Creation using Query 2007

1 Answers