Receiving duplicate values using not equal

[djdaveg]

I am trying to get alll the results of everything in the north where the towns do not match the towns contained in a seperate table, everytime I run this I am getting 37 duplications of each record in the result.

SELECT tbMainData2.ID, tbMainData2.URN , tbMainData2.Con tactName, tbMainData2.Res taurantName, tbMainData2.Add 1, tbMainData2.Add 2, tbMainData2.Add 3, tbMainData2.tow n, tbMainData2.cou nty, tbMainData2.pos tcode, tbMainData2.Tel ephone, tbMainData2.Con tact_Ok, tbMainData2.reg ion, tbMainData2.Res taurantorTakeaw ay, tbMainData2.geo
FROM tbMainData2, NORTHgeo
WHERE tbmaindata2.reg ion='NORTH' And (((tbMainData2. town)<>northgeo .field2))

[ChipR]

Try this:

SELECT * FROM tbMainData2 
WHERE region='NORTH' 
AND 
town NOT IN (SELECT field2 FROM northgeo)

[djdaveg]

Thanks Chip, I have now discovered where I went wrong, it was my syntax, I should have used the following

SELECT DISTINCT tbMainData2.ID, tbMainData2.URN , tbMainData2.Con tactName, tbMainData2.Res taurantName, tbMainData2.Add 1, tbMainData2.Add 2, tbMainData2.Add 3, tbMainData2.tow n, tbMainData2.cou nty, tbMainData2.pos tcode, tbMainData2.Tel ephone, tbMainData2.Con tact_Ok, tbMainData2.reg ion, tbMainData2.Res taurantorTakeaw ay, tbMainData2.geo
FROM tbMainData2, NORTHgeo
WHERE (((tbMainData2. region)='NORTH' & ((([tbMainData2].[town])<>[northgeo].[field2]))));

[ChipR]

The only difference I see between this and the previous SQL you posted are a bunch of unnecessary parentheses and brackets, and using & instead of AND. What do you think was the problem with your syntax?
I would recommend consistent capitalization for clarity in your own project, as well as when posting for others.

[djdaveg]

I changed SELECT to SELECT DISTINCT and yes I changed AND to &. No need for your snotty answer at all thanks very much!!!!, So no more unnecessary replies thanks. sorry for the unnecessary brackets that Access puts in itself when the query is saved

[nico5038]

Dear djdaveg,

I'm a bit confused why you're using this type of language. I hope you do understand that the experts here are investing their spare free time to help others to solve there problems, so some respect is needed. A wrong attitude can lead to experts ignoring your posts.

Well the problem solution you found is a bit odd. The change of the AND into an & tells me that field [northgeo].[field2] consists of the text "NORTH" concatenated with a True or False depending on the town name comparison, something certainly against normalization rules.
The need for a DISTINCT clause tells me that the JOIN of the tables isn't done on a unique field, thus resulting in a Cartesian product.

In a well design data model the Town would have a unique ID and filtering the town table on the field Region should make the solution from ChipR work.
I do see however that you have the Region in the tbMainData2 table and that's also against normalization rules. You run the risk that the same town is recorded in two regions.....

Regards

Nic;o)