MS Access Append Query

[matt_12511]

How can I append/Insert/Update data to a table?

I have a table that 3000 records. Many of the records come from an Excel file so the "Source" field has a value of "Excel ss 2483". I would like to add a new record into another table (customerCodeIndex) for each record in the customer table that has a source = "Excel ss 2483". These new records will be just two fields: customerID and a customercode of "BASE"

My SQL should look something like this:

Insert into CustomerCodeIndex
Set CustomerCodeIndex.ID= Customer.ID, CustomerCodeIndex.CodeID = "BASE"
FROM Customer INNER JOIN CustomerCodeIndex ON Customer.ID = CustomerCodeIndex.ID
Where Customer.Source = "Excel SS 2483"

So far all Access wants to do is change existing records, I need it to add new records.

I have attached to images of my relationship table and my append query.

Thanks

Matt

[matt_12511]

I posted this question on another site and got the answer.

Here is what Stev (from another forum) wrote:

The SQL should be:

INSERT INTO CustomerCodeIndex ( ID, CodeID )
SELECT Customer.ID, "BASE" AS Expr1
FROM Customer
WHERE (((Customer.Source)="Excel SS 2483"));

When you use query builder, put the source tables in the top area and it will ask you for the target table. In this case there is no need to have the target table displayed with the join. It only complicates things unneccessarily.

Thanks to all,

Matt