Need help with unmatching query

[goterps1]

I'm using the unmatching query wizard to find records on table [Fund 49] that do not match records on table [Fund 83]. But I'm not getting the expected results. I'm trying to pull the records from the [Fund 49] table that do match either of the criteria in the FROM clause on the [Fund 83] table. Any suggestions on a better query that I can use to get the results I'm looking for?



SELECT [FUND 49].FUND, [FUND 49].ACCOUNT, [FUND 49].SSN, [FUND 49].FIDUCIARY, [FUND 49].REGLINE1, [FUND 49].REGLINE2, [FUND 49].DEALER, [FUND 49].SOCIALCODE, [FUND 49].DEALERCNTL, [FUND 49].CHECKWRITE, [FUND 49].MULTICWR, [FUND 49].STOPPURCH, [FUND 49].STOPXFER, [FUND 49].OPENCLOSE, [FUND 49].FINS_ID, [FUND 49].AGENT_FOR_FIRM_NBR, [FUND 49].FIRM_ACCOUNT_NBR INTO [ML > 0 NON MATCHING FUND 49]
FROM [FUND 49] LEFT JOIN [FUND 83] ON ([FUND 49].FINS_ID=[FUND 83].FINS_ID) AND ([FUND 49].AGENT_FOR_FIRM_NBR=[FUND 83].AGENT_FOR_FIRM_NBR) AND ([FUND 49].FIRM_ACCOUNT_NBR=[FUND 83].FIRM_ACCOUNT_NBR)
WHERE ((([FUND 83].FUND) Is Null));

 

[ByteMyzer]

Seems like you're doing what you need. From what I can tell, your query is doing a left join between [FUND 49] and [FUND 83] on the following fields:
FINS_ID
AGENT_FOR_FIRM_NBR
FIRM_ACCOUNT_NBR

Your ONE criteria, WHERE [FUND 83].FUND Is Null, means that the query is looking for records in [FUND 49] that do not appear in [FUND 83], which means that the query is doing exactly what you told it to do. If you're not getting any results appended to a new table, that means that there are no records in [FUND 49] for which there are no matching records in [FUND 83], matched by the FINS_ID, AGENT_FOR_FIRM_NBR, FIRM_ACCOUNT_NBR.

So, exactly what results were you expecting, and on what specifically were those expectations based?

 

[Golom]

Change your WHERE clause to

WHERE [FUND 83].[red]FINS_ID[/red] Is Null

You are testing for NULL on a field not involved in the join. There is a possibility that the record containing the field does exist but that particular field is NULL.

 

[goterps1]

I have a total of 2046 rows in [Fund 49]. I received a total of 2 records when I run a query to Match the records based on the same criteria. When I do the NON matching I'm only receiving 2039 records. So I'm missing 5 records. Basically, I want the NON matching to pull back every record that does not match on all 3 fields(FINS_ID, AGENT_FOR_FIRM_NBR, FIRM_ACCOUNT_NBR).

 

[goterps1]

Nevermind... I just think I found my problem. Thanks again for your help.

 

[ByteMyzer]

Care to share with the class?