Query not working with <> operator

[ktrasler]

Hi All

Can't understand why the following doesn't work when changing the operator.

Using the "<>"
SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues, tblTempNew WHERE (((qryColleagues.WCN)<>[tblTempNew]![WCN])) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName;


the particaular part is (((qryColleagues.WCN)<>[tblTempNew]![WCN]))

it works if the operator is "=" but not "<>"

The thing is I'm sure this was working fine.

edit: I have just noticed that this works if there is only one record in the tblTempNew table. As soon as I put 2 records in there it does not work.

Any ideas.

Kev

 

[Rabbie]

Its going to generate an awful lot of hits isn't it. Have you tried

Where (((qryColleagues.WCN) not = [tblTempNew]![WCN]))

 

[ktrasler]

Hi Rabbie

That doesn't work either (unless there is one record)

Kev

 

[neileg]

Can you explain in english what you are trying to do? There is no JOIN in your SQL between qryColleagues and tblTempNew.

 

[ktrasler]

Hi neileg

I want to show the colleagues from the colleagues table that are either in/not in the tblTempNew.

I have tried using the join but get exactly the same result.

Cheers

Kev

 

[Rabbie]

Hi neileg

I want to show the colleagues from the colleagues table that are either in/not in the tblTempNew.

I have tried using the join but get exactly the same result.

Cheers

Kev

You hav a working query that will show the colleagues that are in tblTempNew. Use the Query builder record to find the ones that are not in tblTempNew.

You will find the wizard by going into the Query page and clicking on New

Then click on Find Unmatched Query Wizard.

Good luck

 

[neileg]

This will give you the entries in qryColleagues where there is no entry in tblTempNew

SELECT qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift FROM qryColleagues LEFT JOIN tblTempNew ON qryColleagues.WCN=tblTempNew.WCN
WHERE ((tblTempNew.WCN) Is Null) GROUP BY qryColleagues.WCN, qryColleagues.ClgName, qryColleagues.Shift ORDER BY qryColleagues.ClgName

 

[ktrasler]

Hi both

Thanks for you time with this.

Its working fine now, its also made me tidy up the relationships aswell.

Cheers again.

Kev