Need the results to be the UNmatched records

[Guest]

Help!
I have 2 tables. One has a code and a policy number. There are several
policy records with the same code. The other has a code and a name. These
records are unique. Each table is sorted (by separate queries) by code.

I just want a list of the policy records that did not have a matching code.

I tried using the unmatched query wizard, but that gives me every thing or
nothing. I put the table with policy number as the left table since I want
everything from that table that did not have a matching record from the right
table. What am I doing wrong? Please help!!
Below is the SQL from my query:

SELECT qry_test_pols_ALL_sorted.producer_code,
qry_test_pols_ALL_sorted.policy_number, qry_test_pols_ALL_sorted.producer_name
FROM qry_test_pols_ALL_sorted LEFT JOIN qry_prdcr_code_by_id ON
qry_test_pols_ALL_sorted.producer_code = qry_prdcr_code_by_id.tbl_pc_id
WHERE (((qry_prdcr_code_by_id.tbl_pc_id) Is Null))
ORDER BY qry_test_pols_ALL_sorted.producer_code;

 

[Tom Ellison]

Dear NewBee:

I have rewritten the query for my own reading. I have used Aliases for
those long table names:

SELECT X.producer_code, X.policy_number, X.producer_name
FROM qry_test_pols_ALL_sorted X
LEFT JOIN qry_prdcr_code_by_id Y
ON X.producer_code = Y.tbl_pc_id
WHERE Y.tbl_pc_id Is Null
ORDER BY X.producer_code;

Now, the aboive is based on 2 queries, right?

What specifically is wrong with the results? Is it missing results you were
expecting? Is it returning no results at all?

Find (or create) a row in the "left side" that is missing on the right side.

Does that row appear in qry_test_pols_ALL_sorted? Is it missing in
qry_prdcr_code_by_id?

I expect if you look at the specifics, you'll find the problem.

Tom Ellison

 

[Guest]

Yes the unmatched query was based on 2 other queries. The 2 original queries
sorted the table data. There should have been several records in the left
query that did not match anything in the right query, based on the
information I was importing into my tables.

I'm not sure I quite understand it, but what I found was that I was losing
my records when I imported them into my tables. My 2 original tables are
used for several other things. When I set up a new table to bring in the
data, a table that is currently not being used for anything else, the records
come in fine. The ones I am trying to find in the unmatched query are in the
table. I run the unmatched query and it shows exactly what it should.

I'm assuming that the import is somehow smart enough to know that later in
the process those records would be invalid, so it does not allow them to be
imported. Is this true?