Umatched Query Help

[dazstarr]

Unmatched Query Help

Hi

I have 2 tables with 2 fields - a name and an area.

What I would like to do is compare the area fields and display any that don't match.

eg.

Table1
Name: Bob
Area: 1

Table2
Name: Bob
Area: 2

In this case I would like to see this record as the area fields don't match.

I have tried to use the unmatched query wizard (both ways round) and dont get any results.

Can anyone help me please?

Many Thanks in advance.

 

[RuralGuy]

Post the SQL for what the Unmatched Query Wizard creates that does not work please.

 

[khawar]

SELECT Table1.Name, Table1.Area
FROM Table1 LEFT JOIN Table2 ON (Table1.Area = Table2.Area) AND (Table1.Name = Table2.Name)
WHERE (((Table2.Name) Is Null)) OR (((Table2.Area) Is Null))

 

[MSAccessRookie]

SELECT Table1.Name, Table1.Area
FROM Table1 LEFT JOIN Table2 ON (Table1.Area = Table2.Area) AND (Table1.Name = Table2.Name)
WHERE (((Table2.Name) Is Null)) OR (((Table2.Area) Is Null))

I do not think this query does what dazstarr wants it to. It will only show you entries with NULL values in Table2. Try this instead. It shows the Name and Both areas if they are different.

Select Table1.Name, Table1.Area, Table2.Area
FROM Table1 LEFT JOIN Table2 ON (Table1.Name = Table2.Name)
WHERE (Table1.Area <> Table2.Area);

 

[khawar]

Will show as required by him you can test it on data

 

[MSAccessRookie]

Will show as required by him you can test it on data

What about when Table1.Name=Table2.Name and Table1.Area=1 and Table2.Area=2 as in his example?

 

[khawar]

Download the sample and see it yourself

 

[MSAccessRookie]

What about when Table1.Name=Table2.Name and Table1.Area=1 and Table2.Area=2 as in his example?

Never Mind, I see it now and you are right. It took me a while to work through the details. But I still think that my approach is a little less complicated

 

[khawar]

Actually your approach will show only that record in which name is same but area is different
But what about the situation when area is same and name is different the record will be skipped
as the person asking the question wants to compare both fields the query results will not be accurate

 

[MSAccessRookie]

Actually your approach will show only that record in which name is same but area is different
But what about the situation when area is same and name is different the record will be skipped
as the person asking the question wants to compare both fields the query results will not be accurate

I agree with your assessment regarding this point. I perceived the requirement to be that an individual person could only have one area, not the other way around. If the converse is also a requirement, then your approach will produce exactly what is being requested.

 

[dazstarr]

Thanks

Hi Guys

Thanks for your replies, i decided to leave work early and get drunk.

I will give them a try tomorrow.

Thanks again

Daz

 

[dazstarr]

Tested and Works!

Hey Guys

I tested this and it works!

Many Thanks!!!

 

[MSAccessRookie]

I am glad that you were able to find help in this forum. I have been helped many times and always like to return the favor when I am able.