Query duplicates and blank (1 Viewer)

I think I should know this...

I'm trying to find duplicates records on two table and blanks. I've linked two tables and it shows all the records that are in both tables. But I can't get it to include all the records that are empty in the ecp effectivity column.

use Left Join, maybe it will help.

Something weird is happening. Instead of having a line between linked items, it's only showing a dot beside each item.

Thanks for your help. I'll do a work around, make two queries and go from there.

hllary said:

Something weird is happening. Instead of having a line between linked items, it's only showing a dot beside each item.

Thanks for your help. I'll do a work around, make two queries and go from there.

Click to expand...

If you need more help, consider posting a sample db. Good luck!

I can't get it to include all the records that are empty in the ecp effectivity column

Click to expand...

Your words and code can never line up. Regardless of your WHERE clause, that INNER JOIN clause will never allow null [ECP Effectivity] records through. You cannot JOIN on null values, so if [ECP Effectivity] is null, it will never pull records back from that table.

I suggest you post sample data to demonstrate your issue. Please provide 2 sets:

A. Starting data from your tables. Include table and field names and enough data to cover all cases.

B. Expected results of A. Show what data you expect your query to return when you feed it the data from A.

Again, 2 sets of data--starting and ending.

You have a syntax/logic error in your query.
1. You cannot use "= Null" to test for null. No conditional statement where one object is null will EVER return true EXCEPT for the IsNull() function or the SQL predicate "Is Null".

See the changes below. I removed the extraneous parentheses since they just confuse the statement without adding any meaning.

SELECT REV1Val_WP_Tracking.*
FROM REV1Val_WP_Tracking INNER JOIN TM_tbl ON REV1Val_WP_Tracking.[ECP Effectivity] = TM_tbl.TrackingID
WHERE REV1Val_WP_Tracking.[ECP Effectivity] = [TM_tbl].[TrackingID] OR REV1Val_WP_Tracking.[ECP Effectivity] Is Null;

Now, this may still not be logically correct but at least it is syntactically correct.