Query of 2 fields that do not occur in another table

[F.M.]

Hi there,



I'm struggling with a query that should show all records if both 2 fields in that table do not match one field in another table.

Table 'Callviewer' has severall fields, but 2 of them 'WeCalledThemNr' and 'TheyCalledUsNr' contain telephonenumbers. I want to check these numbers with table 'Checknr' field 'PhoneNr'. If both numbers do not match with 'Phonenr' I want that record to be shown in the query.

When I both leftjoin the fields from CallViewer to CheckNr it reacts like and OR statement.... so 'Show the record if WeCalledThemNr is not equal to PhoneNr' or 'Show the record if TheyCalledUsNr is not equal to PhoneNr' .... so actually every record ends up in the query.


SELECT [Callviewer].[WeCalledThemNr], [Callviewer].[TheyCalledUsNr]
FROM [Callviewer] LEFT JOIN CheckNr ON [Callviewer].[WeCalledThemNr] = CheckNr.[PhoneNr] AND [Callviewer].[TheyCalledUsNr] = CheckNr.[PhoneNr]
GROUP BY [Callviewer].[WeCalledThemNr], [Callviewer].[TheyCalledUsNr], CheckNr.PhoneNr
HAVING (((CheckNr.PhoneNr) Is Null));


Anyone a clue how to make sense?

Thanks for your time and attention.

[ranman256]

if you are doing 2 fields, you may not get the result you think. The logic is prob: not in Fld1 but is in Fld2 and vice versa.
Try doing only 1 at a time.
Q1 to find whats not in fld1
Q2 using Q1 to find , not in fld2

[F.M.]

Refreshingly easy point of view.... Thnx Ranman!