Appending Data to Existing Table

[tannagirl]

I have an existing table that was initially populated using a make table query. Now that the data is there I want to insert new rows only if the primary key (matter_no) is not existing. I assumed that an append query would do this, but apparently an append table just adds the data again, so I have duplicate rows for each matter_no.

Our DBA sent me the following insert statement, but it does not seem to work in Access.

SELECT MM.MATTER_NO, MM.MATTER_NAME
FROM MM, MKP, CHECKLIST_MATTER_NO
WHERE ( MM.MATTER_STATUS="A" AND MKP.KP_ID="269629" )
AND NOT EXISTS (MM.MATTER_NO = CHECKLIST_MATTER_NO.MATTER_NO)

 

[Jon K]

Since your original append query works, what you need is add the criteria (i.e. a Where Clause) in the append query:-

INSERT INTO ExistingTable
SELECT ......
FROM ......
WHERE Matter_No not in (Select Matter_No from ExistingTable);


In fact, you can first get rid of the duplicates in the existing table, and then in table design set Matter_No as Primary Key, Indexed: Yes (No Duplicates). This can avoid appending any duplicate Matter_No in the future (just click Yes in the future when a message pops up).

 

[tannagirl]

Append Query Edit

Okay, I modified the append query to include the not in select statement.

INSERT INTO checklist_matter_no ( MATTER_NO, MATTER_NAME, MATTER_STATUS )
SELECT MM.MATTER_NO, MM.MATTER_NAME, MM.MATTER_STATUS
FROM MM INNER JOIN MKP ON MM.MATTER_NO = MKP.MATTER_NO
WHERE (((MKP.KP_ID)="269629") AND ((MM.MATTER_STATUS)="A")) AND ((MM.MATTER_NO NOT IN (SELECT CHECKLIST_MATTER_NO.MATTER_NO)));

When I run the above I receive the following error:

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing or the punctuation is incorrect."
Obviously, I have not written it correctly. What is wrong?

 

[pdx_man]

SELECT CHECKLIST_MATTER_NO.MATTER_NO FROM ExistTbl)));

 

[tannagirl]

Append Successful

THANK YOU!!!