473,320 Members | 1,823 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Receiving duplicate values using not equal

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.ContactName, tbMainData2.RestaurantName, tbMainData2.Add1, tbMainData2.Add2, tbMainData2.Add3, tbMainData2.town, tbMainData2.county, tbMainData2.postcode, tbMainData2.Telephone, tbMainData2.Contact_Ok, tbMainData2.region, tbMainData2.RestaurantorTakeaway, tbMainData2.geo
FROM tbMainData2, NORTHgeo
WHERE tbmaindata2.region='NORTH' And (((tbMainData2.town)<>northgeo.field2))
Jan 20 '10 #1
5 1945
ChipR
1,287 Expert 1GB
Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM tbMainData2 
  2. WHERE region='NORTH' 
  3. AND 
  4. town NOT IN (SELECT field2 FROM northgeo)
Jan 20 '10 #2
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.ContactName, tbMainData2.RestaurantName, tbMainData2.Add1, tbMainData2.Add2, tbMainData2.Add3, tbMainData2.town, tbMainData2.county, tbMainData2.postcode, tbMainData2.Telephone, tbMainData2.Contact_Ok, tbMainData2.region, tbMainData2.RestaurantorTakeaway, tbMainData2.geo
FROM tbMainData2, NORTHgeo
WHERE (((tbMainData2.region)='NORTH' & ((([tbMainData2].[town])<>[northgeo].[field2]))));
Jan 20 '10 #3
ChipR
1,287 Expert 1GB
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.
Jan 20 '10 #4
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
Jan 21 '10 #5
nico5038
3,080 Expert 2GB
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)
Jan 22 '10 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

44
by: Xah Lee | last post by:
here's a large exercise that uses what we built before. suppose you have tens of thousands of files in various directories. Some of these files are identical, but you don't know which ones are...
4
by: MP | last post by:
I need a code in the Before Update event procedure, which prevents to enter duplicate record in a form. I have the main formX, which takes values from the table named tblX and a subformY, which...
2
by: lewie | last post by:
I am trying to construct a semi duplicate query. I want it to match 2 fields and check a range for the 3rd match as follows date = date, title = title, and start time between start time and end...
9
by: vbportal | last post by:
Hi, I would like to add BitArrays to an ArrayList and then remove any duplicates - can someone please help me forward. I seem to have (at leaset ;-) )2 problems/lack of understanding (see test...
5
by: Manish | last post by:
The topic is related to MySQL database. Suppose a table "address" contains the following records ------------------------------------------------------- | name | address | phone |...
4
by: FangQ | last post by:
hi I am very new to mysql. I have a question about using the "on duplicate update" clause with insert command. my table "data" has two columns, field1 and field2, where field1 is the index...
6
by: reppisch | last post by:
Hi Ng, I have a multiset for keeping elements sorted in a container but key values may also be equal. Is there any guaranteed traversal order within the duplicate keys of a multimap? When...
3
by: rajeshkrsingh | last post by:
Hi friends, Step1- create table duplicate ( intId int, varName varchar(50) ) insert into duplicate(intId,varName) values(1,'rajesh') insert into duplicate(intId,varName) values(2,'raj12')...
26
by: neha_chhatre | last post by:
can anybody tell me how to compare two float values say for example t and check are two variables declared float how to compare t and check please help me as soon as possible
9
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.