ChadT
ChadT

Reputation: 92

Access Append Query duplicates results

I have an Append Query

INSERT INTO SPLIT_EXPORT_X2_LOANNOTPRESENT
SELECT SPLIT_EXPORT_X2_2.*
FROM SPLIT_EXPORT_X2_2, IMPORT_CSV
WHERE (((SPLIT_EXPORT_X2_2.coll_key) Not In (Select [LoanIdentifier] from [IMPORT_CSV])));

It is supposed to append anything from the SPLIT_EXPORT_X2_2 table which doesnt appear in IMPORT_CSV based on the loan identifier.

There are many columns in the X2 table and i need them all appended.

As an example of the problem, if IMPORT_CSV table has 20 rows in it and 1 row is to be appended. it will append 20 copies of that row. it seems like its doing the WHERE check on every row and appending when it doesnt match. usually a group by solves this but access is informing me that i can't group by if i'm appending by asterisk. i would have to list out all the columns and give the particular one a group by.

Is there something else that can be done in lieu of that?

Upvotes: 0

Views: 181

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You don't need IMPORT_CSV in the FROM clause, only in the subquery:

INSERT INTO SPLIT_EXPORT_X2_LOANNOTPRESENT
    SELECT SPLIT_EXPORT_X2_2.*
    FROM SPLIT_EXPORT_X2_2
    WHERE (((SPLIT_EXPORT_X2_2.coll_key) Not In (Select [LoanIdentifier] from [IMPORT_CSV])));

I also recommend using NOT EXISTS instead of NOT IN:

INSERT INTO SPLIT_EXPORT_X2_LOANNOTPRESENT
    SELECT SPLIT_EXPORT_X2_2.*
    FROM SPLIT_EXPORT_X2_2
    WHERE NOT EXISTS (SELECT 1
                      FROM IMPORT_CSV
                      WHERE SPLIT_EXPORT_X2_2.coll_key = IMPORT_CSV.[LoanIdentifier]
                     );

NOT IN will filter out all rows if any value in the subquery is NULL.

Upvotes: 2

Related Questions