Using <> Giving me Records it should not

[Jamet1234]

I have a query that uses "<>" to find records that are not equal between 2 tables. The formula looks like this:

<> [tblWorkPerformed]![ID]

It works fine as long as there is only one record in tblWorkPerformed, but once I add a second record I get the records I dont want plus the records I do. The records I do what are there twice. I found how to only get single records of each, but it still desplays the records it should not. I tried a dummy database, and it does the same thing. Do I have something wrong or is this just not possible.

Thanks
Anthony

 

[RuralGuy]

Have you tried letting the Query Wizard create a "Find Unmatched" query for you and see what the SQL looks like?

 

[Jamet1234]

Rural Guy,

No. I have looked at the sql of my query though. It weird how it works as long as the table has only one record, but then does not once new records are added.

How do you make the wizard create an find unmatched I have never seen that?

 

[RuralGuy]

Go to the Queries section of the Database window and press the New button and select the wizard you want.

 

[Brianwarnock]

If you click queries, new, and select Unmatched query wizard it will lead you through the steps.

Brian

 

[Jamet1234]

Thanks Guys

I guess I did not pay attention never saw that in the wizard list before. Still weird how it works perfect that way but not when I do it from design. It may be the syntax is <=> not <> at least that is how the wizard displayed it.

Thanks
Anthony

 

[EMP]

Still weird how it works perfect that way but not when I do it from design.

When two tables in a query are Not Joined by the = sign, each record in the first table is matched with each record in the second table.

That is why when tblWorkPerformed contains more than one records it returns more records than you would expect as they all fulfil the condition Table1.ID<>tblWorkPerformed.ID.

^