Rob
Rob

Reputation: 2472

Validating bad imported data in MS Access

The scenario is typical. User asks me to import a spreadsheet into a table - Mastertable.

Mastertable has a PK of acct and FK of ProviderID.

The import goes poorly, about 10% of records lost due to key violations. I think this is due to specifying a providerID that does not exist.

Is there a way I can figure out which providerIDs are bad? I can import into a fresh table - "MasterTableNew", but when I do a select like this:

SELECT acct FROM MastertableNew 
WHERE NOT EXIST (
   SELECT Mastertable.acct 
   FROM Mastertable 
   INNER JOIN MastertableNew 
   ON Mastertable.acct = MastertableNew.acct)

it turns up blank - I am expecting to see rows that exist in MastertableNew that do not exist in Mastertable.

Upvotes: 0

Views: 61

Answers (1)

Fionnuala
Fionnuala

Reputation: 91376

How about:

SELECT acct FROM MastertableNew 
LEFT JOIN Mastertable ON MastertableNew.acct = Mastertable.acct
WHERE Mastertable.acct Is Null

Upvotes: 1

Related Questions