Cross Reference two lookup columns

I'd suggest a table with these data fields:

ProviderID (foreign key to Providers table)
CityID (foreign key to Cities table)
ServiceID (foreign key to Services table)

One record for each combination of a provider in one city with one service.
If a provider has three services in one city, three records. If a provider
has one service each in three cities, three records.

This also allows you to use this table structure as you add cities and add
services, without having to add fields to the table.

--

Ken Snell
MS ACCESS MVP
http://www.accessmvp.com/KDSnell/



"krumlov" u54746@uwe wrote in message news:9c0bb59aaecfa@uwe...
I am working on a 2007 database of service providers in different cities.
I
have separate tables for services, cities and providers. In the provider
table I have two lookup columns pointing back to services and cities, to
show
the services one offers and in which cities they operate.

The issue is that a provider won't offer all of their services in every
city
the operate in. I need a way to cross reference the cities list with the
services list, and create a new field to hold a yes\no entry for a given
service in a given city.

I am wondering what my options are and what the best way to do this is.
Should I create a new table that queries the lookup columns (for all I
guess)
and then use the queries to provide lookup data for a field back in the
provider table? That (if it would even work) would give my "service in a
given city" data two numeric lookup values which is fine, but should I be
doing yes\no? I eventually want to have a form with check boxes that I
can
use to input or display the data like below. Anyone have any suggestions
on
how to proceed?

[Services]
[Cities] (both lookup columns in same table)
| Phone | Internet | AV | LAN | WAN |
Atlanta x x
Boston x x
Chicago x x x x
Cincinnati x x
Dallas x x x x x