Region grouping

[Guest]

I'm interested in grouping nearby cities into a region and running a query to
select that region by entering in just a city. Currently I am able to select
just city at a time with manipulating criteria selection and understand I
should properly use a string search feature. How should this combination of
the two intermingle for the function to operate properly?

 

[Chaim]

It seems that you would need some way of defining the region. Say a table
with cityname and region designator. Then you would be able to determine the
cities in the region with a self join as in:

select RDT1.cityname
from [Region Definition Table] as RDT1 INNER JOIN
[Region Definition Table] as RDT2 ON
RDT1.[region designator] = RDT2.[region designator]
where RDT2.cityname = [Enter a city name in the region];

(ALL AIR CODE! TOTALLY UNTESTED IN THE REAL WORLD!)

That (or something very similar) should allow you to get all of the cities
in the region based on the name of any city in the region, assuming 1) that
the city names are unique; and 2) how accurately the cityname/region
assignments are entered.

Good Luck!