TABLE DATA COMPARISON

[fsweb2002]

Hi, this might be something simple but I can´t manage to make it work...

I have 2 tables, "Cities" and "New DAta"

Cities has a field called CITY

New Data has a field called CITY.

I am trying to get a query to show me CITY field in "New Data" NOT finding a match CITY in "Cities".

Could anyone please help ???
(sql is fine)

 

[GoDawgs]

The easiest answer is that when you select "New" on the query menu in Access there's a Find Unmatched Query Wizard...that will walk you through.

Kevin

 

[fsweb2002]

Sometimes I amaze myself...

Here I am thinking about some difficult sql combination and the Wizard does it for me...

Thank you so much!

 

[Quehay]

Wizards are fast if you don't know the SQL, but you'll be so much better off knowing the SQL

You want to use an OUTER JOIN


SELECT t.City
from Cities t, NewData
LEFT JOIN on t.City = NewData.City
Where NewData.City is null

This shows you all the Cities values that don't find a match in the NewData. The LEFT is the same as OUTER, it just corresponds to the sequence in which you name the tables in the FROM. Using the where clause is what gives you the unmatched values, otherwise you'd see all values in cities independent of whether they find a match field in NewData.

In the Access Query GUI you'd Right-Click and Edit Join Properties on the join lines between tables, selecting "Show all values from Cities", etc.

 

[fsweb2002]

Thanks Quehay !

Yes, I prefer SQL, as I like using code to do my queries, etc etc.

Your help is most appreciated!
I´ll put it into practice straight away!
Regards