Identifying records causing Key Violation

[MM12]

Access 2010

I am importing a text file into a table that has 6 primary key fields. There are some records that will not import due to key violations, and I expected that. I want to identify those records. I thought Access created a list of rejected records, but I don't see that happening.

To identify the records that are rejecting I tried creating a new table without key fields and importing the text file into that second table. I tried creating an unmatched query in design view with the larger table on the left and the smaller table on the right. I joined all fields using option 2. In the table that has related records I cleared the Show row and typed IS Null in the Criteria row as per some instructions I found online. I've had some success with this in the past, but all I am getting is all of the records from the larger table.



Any suggestions on how to identify records that are causing key field violations?

[JoeM]

Can you switch your query to SQL View and copy and paste your SQL code here?

[ssanfu]

Why an unmatched query. Wouldn't a find duplicates query be better to find duplicate keys? You would have to create a query for each of the key fields...

Maybe I am missing something.....

[orange]

a table that has 6 primary key fields.

You mean a compound primary key composed of 6 fields?

[MM12]

Yes, it is a compound primary key composed of 6 fields. I know I was able to get the unmatched on multiple fields query to work in Access 2003. I seem to remember having to use nz at that time but don't remember how I used it. I am looking for the records NorthportPrograms that do not have an exact match in Copy of NorthPortPrograms. Six of the fields are key fields and must have data. The other fields can be empty.

This is the SQL.
SELECT NorthportPrograms.Field1, NorthportPrograms.Field2, NorthportPrograms.Field3, NorthportPrograms.Field4, NorthportPrograms.Field5, NorthportPrograms.Field6, NorthportPrograms.Field7, NorthportPrograms.Field8, NorthportPrograms.Field9
FROM NorthportPrograms LEFT JOIN [Copy Of NorthportPrograms] ON (NorthportPrograms.Field1 = [Copy Of NorthportPrograms].Field1) AND (NorthportPrograms.Field2 = [Copy Of NorthportPrograms].Field2) AND (NorthportPrograms.Field3 = [Copy Of NorthportPrograms].Field3) AND (NorthportPrograms.Field4 = [Copy Of NorthportPrograms].Field4) AND (NorthportPrograms.Field5 = [Copy Of NorthportPrograms].Field5) AND (NorthportPrograms.Field6 = [Copy Of NorthportPrograms].Field6) AND (NorthportPrograms.Field7 = [Copy Of NorthportPrograms].Field7) AND (NorthportPrograms.Field8 = [Copy Of NorthportPrograms].Field8) AND (NorthportPrograms.Field9 = [Copy Of NorthportPrograms].Field9)
WHERE ((([Copy Of NorthportPrograms].Field1) Is Null) AND (([Copy Of NorthportPrograms].Field2) Is Null) AND (([Copy Of NorthportPrograms].Field3) Is Null) AND (([Copy Of NorthportPrograms].Field4) Is Null) AND (([Copy Of NorthportPrograms].Field5) Is Null) AND (([Copy Of NorthportPrograms].Field6) Is Null) AND (([Copy Of NorthportPrograms].Field7) Is Null) AND (([Copy Of NorthportPrograms].Field8) Is Null) AND (([Copy Of NorthportPrograms].Field9) Is Null));

[JoeM]

I don't think you want to use the 3 non-key fields which may be blank in your join (or maybe that is where you were using NZ before?), i.e.

...AND (NZ(NorthportPrograms.Field7,"0")) = NZ([Copy Of NorthportPrograms].Field7,"0")) AND ...

Also, in you WHERE clause, you only need one condition:

WHERE ([Copy Of NorthportPrograms].Field1) Is Null;

If the record is unmatched (meaning it does not exist on the right side, you only need to check for the existence of one field on the right side.

[MM12]

Thank You! I got it to work using your suggestions.
1) Used 1 condition in wHERE clause
2)Included all fields that contain data in query but used NZ only on those fields that may be blank.