Cross Reference two lookup columns

[krumlov]

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

 

[Ken Snell [MVP]]

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.

 

[krumlov via AccessMonster.com]

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.

I am working on a 2007 database of service providers in different cities. [quoted text clipped - 30 lines]
Cincinnati x x
Dallas x x x x x

Thanks a lot for the help Ken. I had thought of doing it that way but I
actually have over 200 services classifed. I'm worried that would create too
many records. Also, Whe I add a new provide I want to be able to add the
services they offer by default, and then take away what the don't offer in
what cities. I'm still looking for a way around this. Maybe a crosstabs query?

 

[Douglas J. Steele]

Thanks a lot for the help Ken. I had thought of doing it that way but I
actually have over 200 services classifed. I'm worried that would create
too
many records. Also, Whe I add a new provide I want to be able to add the
services they offer by default, and then take away what the don't offer in
what cities. I'm still looking for a way around this. Maybe a crosstabs
query?

Using Ken's approach is definitely the correct way to do it.

"Too many records" is seldom, if ever, a consideration (remember that each
record is going to be very small). If you don't do it that way, then what
will you do when suddenly you have a 201st or 202nd service? You'd have to
change your table, and therefore all related forms and reports.

A crosstab of that properly designed structure would give you the layout for
which you're looking.

 

[John W. Vinson]

Thanks a lot for the help Ken. I had thought of doing it that way but I
actually have over 200 services classifed. I'm worried that would create too
many records.

When your table gets over 2,000,000 records you can start getting concerned.
200 services x 100 cities is 20,000 records - a total non-issue for Access.

Also, Whe I add a new provide I want to be able to add the
services they offer by default, and then take away what the don't offer in
what cities.

A simple append query from the table of services would do this easily.

I'm still looking for a way around this. Maybe a crosstabs query?

A crosstab query is a good output/display medium but it's not updateable and
not suitable for what you're trying to accomplish.

 

[Fred]

Hello Krumlov,

I noticed that nobody answered. There was some misused terinology in your
post,, but more importantly 98% of your post is talking about final trimmings
instead of your important steps and info that needs to be provided and
handled at this point including

#1 nature and structure of your process & data. For example, does every
provided provide all of the listed service, but just not in every city?
#2 Structure of your tables.
#3 In plain English (no-database terms) what is the mission of this
database. Some ideas of this might be:

-Record a list of services taht you care about
-Record a list of cities that you care about
-Record a list of providers
-Create or record assignmets, contracts, instances of coverage, instances or
orders for services, instances of services provided etc.

Hope that helps a little