Cities are Equal but Region Number is not equal

[Billiam]

I have a table with cities and regions. I would like to run a query which
will identify duplicate cities that have different region Numbers, in order
to verify a city has not been placed in two different regions.

What is the easiest way to do this?

I tried a select staement but it does not like my syntax.

SELECT City, RegionNumber
FROM T_A
WHERE T_A.City = T_A.City AND RegionNumber DOES NOT EQUAL RegionNumber

any help appreciated!!!!
Thanks,
Billiam

 

[Douglas J. Steele]

SELECT City, RegionNumber
FROM T_A
WHERE City IN (SELECT City
FROM T_A
GROUP BY City
HAVING Count(*) > 1)

 

[Billiam]

Hi Ken,

Thank you very much for responding, and especially with so much detail...it
really is helpful to us newbies!

May I ask where I would place this expression in a query design...I suspect
in the criteria field. (It is my goal to have a printable list showing
duplicate cities in different regions so that a correction can be made).

When I place:

SELECT City, RegionNumber
FROM T_A As T_A1
WHERE EXISTS
(SELECT *
FROM T_A As T_A2
WHERE T_A2.City = T_A1.City
AND T_A2.RegionNumbr <> T_A1.RegionNumber);

in a blank query design in the criteria field based on table A, I get the
following syntax error:

The syntax of the subquery in this expression is incorrect. Check the
subquery's syntax and enclose the subquery in parentheses.

Which part is the subquery?

As to the proper normalization of city and region, I understand the point,
but do not understand how to make it happen in a data entry form...I am
afraid I have not been able to grasp the whole converting an ID key to
english for the data entry person in a form and subform...oh well, I will
keep trying!

For example, i understand that resolving a many to many relationship
requires a junction table made up of the primary keys of the 'one sides'.
Then you have this wonderful record, but it is made up of unintelligible ID's
to the data Entry person...in other words, how do I convert that wonderful
record into something intelligible to an enduser?

Anyways, I will definately see if I can make sense of how to accomplish it
all by looking at the link you provided! Thank you very much again for you
very kind help.

Best Regards,

Billiam

 

[John W. Vinson]

May I ask where I would place this expression in a query design...I suspect
in the criteria field. (It is my goal to have a printable list showing
duplicate cities in different regions so that a correction can be made).

When I place:

SELECT City, RegionNumber
FROM T_A As T_A1
WHERE EXISTS
(SELECT *
FROM T_A As T_A2
WHERE T_A2.City = T_A1.City
AND T_A2.RegionNumbr <> T_A1.RegionNumber);

This is *the entire query*, not a criterion. The query design window is just a
tool to help construct SQL; this SQL text is the *real* query.

Create a new query in the design window. Don't select any tables. instead
select View... SQL to go into the SQL window; copy and paste this text into
the window, replacing the

SELECT;

that will be there.

Change any table or fieldnames to match those in your database.

 

[Billiam]

Thank you John, I thought there was a SQL window there somewhere!

It worked perfectly once I placed it in the SQL VIEW location. Thanks very
much for jumping in as I have been hoping someone would answer so I can leave
work!!!

Yahoo...it's Miller Time, LOL!

 

[John W. Vinson]

Yahoo...it's Miller Time, LOL!

Enjoy... I think I'l have a Leinenkugel though... <g>