Query with criteria problems 1

[FeS2]

Access 2007

I am having trouble with a query that I think should be an easy fix but I can't seem to find it.

I have 2 tables, the 1st table is a list of telephone number prefixes and the cities they cover, the 2nd table is a list of phone numbers and cities. My goal is to find where a phone number from table 2 has a prefix that it shouldn't have since it is not in the covered city. The big issue is that there can be multiple prefixes covering the same areas and there can be multiple cities covered by those prefixes.

Example
Table 1
222 FX Anch
222 Anchorage
263 FX Anch
263 Anchorage

Table 2
263 2639999 FX Anch
263 2638888 Anchorage
222 2229999 FX Anch
222 2228888 Douglas

I have tried joining the 2 tables on the prefix and then using the criteria to say if table_1.city <> table_2.city to return the ones that are in the wrong city. It actually returns most of the records when it should only return 1, the one in Douglas.

I tried Exists on the city but the queries failed, either way to many records, no records or just pops up an error.

Please help, can someone point me in the right direction to solve this?

Thank you for your help in advance.

FeS2

 

[SkipVought]

hi,

SELECT t2.exch, t2.phone, t2.city

FROM [Table 2] t2

where t2.city not in (select t1.city from [Table 1] t1 where t1.exch=t2.exch)

I get Douglas row.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[FeS2]

Thank you SkipVought, that works perfectly.

FeS2