If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cross Reference two lookup columns
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 |
#2
|
|||
|
|||
Cross Reference two lookup columns
Ken Snell MVP wrote:
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" u54746@uwe wrote in message news:9c0bb59aaecfa@uwe... 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? -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Cross Reference two lookup columns
"krumlov via AccessMonster.com" u54746@uwe wrote in message
news:9c0f7b11ddbce@uwe... Ken Snell MVP wrote: "krumlov" u54746@uwe wrote in message news:9c0bb59aaecfa@uwe... I am working on a 2007 database of service providers in different cities. Cincinnati x x Dallas x x x x x 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. 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) |
#4
|
|||
|
|||
Cross Reference two lookup columns
On Sun, 13 Sep 2009 14:21:27 GMT, "krumlov via AccessMonster.com" u54746@uwe
wrote: 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. -- John W. Vinson [MVP] |
#5
|
|||
|
|||
Cross Reference two lookup columns
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 |
Thread Tools | |
Display Modes | |
|
|