Query using operator <>

[npatil]

Hi,

I have the foll. querydef, which using a table Gd, which is already clustered based on a similar querydef.

SELECT Gd.Count, Gd.Field2, Gd.Field3, Gd.Field13, Gd.Field15, Gd.Field15_2, Gd.Field23, Gd.Field31, Gd.Field35, Gd.Field36, Gd.Field41, Gd.Field45, Gd.Field46, Gd.Field51, Gd.Field55, Gd.Field57, Gd.Field58, Gd.Field59, Gd.Field60, Gd.Field61, Gd.Field62, Gd.Field64, Gd.Field65, Gd.Field67, Gd.Field73, Gd.Field82, Gd.Field91, Gd.Field97, Gd.Field108, Gd.Field110, Gd.Field112, Gd.Field117_2, Gd.Field119, Gd.Field124, Gd.Field128, Gd.Field128_2, Gd.Field129, Gd.Field133, Gd.Field138, Gd.Field145, Gd.Field146, Gd.Field147, Gd.Field148
FROM Gd
WHERE (((Gd.Field15_2)=[Field15_2]) AND (Left([Field31],1)=Left([Field31],1)) AND ((Gd.Field35)=[Field35]) AND ((Gd.Field46)=[Field46]) AND ((Gd.Field57)=[Field57]) AND ((Gd.Field58)=[Field58]) AND ((Gd.Field59)=[Field59]) AND ((Gd.Field60)=[Field60]) AND ((Gd.Field61)=[Field61]) AND ((Gd.Field62)=[Field62]) AND ((Gd.Field64)=[Field64]) AND ((Gd.Field65)=[Field65]) AND ((Gd.Field67)=[Field67]) AND ((Gd.Field73)=[Field73]) AND ((Gd.Field82)=[Field82]) AND ((Gd.Field91)=[Field91]) AND ((Gd.Field97)=[Field97]) AND ((Gd.Field108)=[Field108]) AND ((Gd.Field110)=[Field110]) AND ((Gd.Field112)<>([Field112])) AND ((Gd.Field117_2)=[Field117_2]) AND ((Gd.Field119)=[Field119]) AND ((Gd.Field124)=[Field124]) AND ((Gd.Field128_2)=[Field128_2]) AND ((Gd.Field133)=[Field133]) AND ((Gd.Field138)=[Field138]) AND ((Gd.Field145)=[Field145]) AND ((Gd.Field146)=[Field146]) AND ((Gd.Field147)=[Field147]) AND ((Gd.Field148)=[Field148]));


Although I do have records where all the fields except Field112 are equal/ satisfy the criteria, I am getting NO records for the above query. I am not able to understand this. I also checked in my table Gd. and I spotted two records which exactly matched the above query criterion.

 

[JamesMcS]

Looks like your bracketing is a bit messed up. Also very bad practice to leave all the fields named this way...

What does it look like in query design view? As you would expect?

 

[boblarson]

Looks like you should be using JOINS instead of all of the stuff in the Where Clause. And I also question the field naming, but perhaps there is a good reason which we don't know.

 

[npatil]

Looks like you should be using JOINS instead of all of the stuff in the Where Clause. And I also question the field naming, but perhaps there is a good reason which we don't know.

Can you give me a small example of this. I have never used (or had to use joins) so far. Can I use joins even for fields from a single table?

Thanks,

 

[npatil]

Looks like your bracketing is a bit messed up. Also very bad practice to leave all the fields named this way...

What does it look like in query design view? As you would expect?

Ahh...I corrected the brackets, but it still does not work. Yea, I built this is query design and have just copied the SQL statmt here.

 

[boblarson]

Can I use joins even for fields from a single table?

Okay, now you have me really confused. If you have a single table then what are you trying to compare each field against? As written it would be comparing against itself.

If there is only a single table then this:
WHERE (((Gd.Field15_2)=[Field15_2])

is the same as

WHERE (((Gd.Field15_2)=Gd.[Field15_2])

And that makes no sense whatsoever. Are you trying to compare versus some controls on a FORM?

 

[npatil]

Well, I am comparing each field agains itself and am trying to cluster the "like values" together to form clusters of data of this table "Gd" which has about 192 columns and 3000 odd rows. I had tried using the same using two tables, which are identical, but I have tried this method and so far it has worked fine. But you are right, for clustering them, I am using the column Count to index them, and here I am using two recordsets pointing towards the same data, and comparing them with the same "rules" to cluster the records in the table.

So I guess, <> would not work for WHERE (((Gd.Field112)<>[Field112]).

In that case, I should use two tables right?

 

[boblarson]

Clustering them together would simply need the grouping function. You wouldn't need to compare them against themselves.