Table Question

[Database Challenged]

Howdy!

I am in a quandry. I need to define terms in a drop-down menu inside my database. Let me try to explain fully.

I am creating a list of Territories/Regions found in contractual agreements. These Terr/Reg can be: Italian-speaking worldwide which is defined as Argentina, HolySee, Italy, Libya, Monaco, San Marino, Somalia, and Switzerland.

Is there a way I can define "Italian-speaking" inside the database? So when someone selects Italian-speaking the countries that area is defined as will appear? Is that overly complicated? Could I use a subdatasheet for this?

Or will it always require a "cheat sheet" for the person entering/reviewing the data? It could be a bigger issue when running reports. Reading the report will require a degree in how we define our Territories/Regions!

Thanks!

 

[G37Sam]

You'll need to have a field in your table that lists out all the countries you'll be using, a check box maybe? MS Access doesn't know what countries speak Italian

Then you can apply filters/queries

Hope that helps

 

[gfultz]

There are a few ways you could handle this, depending on what you want to do with the data.

The first thing you need is a table which stores the Territories with a PK (Autonumber is fine). For each contract, you will store the territory PK as a FK. For the form, you create a combo box that allows you to use the Territory table as the record source, showing the Territory Description and binding the value to the PK.

For reports, you could go a step further and add one more field to Territory table where you list each country in the territory or, and the better way, you create one more table which stores each country with a PK and uses the PK of the territory to identify which territory it belongs to.

You must define relationships (One territory could have multiple countries and multiple contracts) for this data and there are also things like referential integrity, cascading updates and deletes that you will want to look into to properly setup your database.

I have setup a really simple MDB with one of the forms setup for utilizing a combo box to select the territory. If you wanted to make the countries visibile on a form, you could use a subform to populate based on that value and much more.

 

[Database Challenged]

For each contract, you will store the territory PK as a FK. For the form, you create a combo box that allows you to use the Territory table as the record source, showing the Territory Description and binding the value to the PK.

For reports, you could go a step further and add one more field to Territory table where you list each country in the territory or, and the better way, you create one more table which stores each country with a PK and uses the PK of the territory to identify which territory it belongs to.

Wow. Thanks for your help....and especially for providing the sample database. I'm best as a visual learner.

 

[gfultz]

No Problem. Not much of a sample db but it should get you started. Good Luck!

 

[Database Challenged]

It's me again! New week, new question.

I am going to implement the changes suggested in the sample database provided by gfultz. In doing so, I need to modify field names. What we currently have as Countries needs to be called Territories. It seems easier in my mind to fix the naming issue there as that is the table which is used in all the coding and ultimately where we want to be able to put the Territories. I will define the countries within the territories in another table.

Is there an easy "Find and Replace" way to do this within the coding for the database? My fear is that I'll change a name to avoid confusion and then will "break" the database again. Or, is it easier to just think up some different name for the table where I will define the Territories out?

Thanks in advance!

 

[Database Challenged]

One more question.

A country might appear in more than one Territory. I should include more than one column to define those Territories, shouldn't I? For example, Egypt will be found in the following Territories: World, Arabic-Speaking, and Northern Africa. For this one, I would need three columns: it would be TerritoryID_1, TerritoryID_2, and TerritoryID_3.

Is this plausible? Does it make sense?

 

[G37Sam]

For your first issue, it should be fine, you just need to rename it in the query as well

For the second, (from the sound of it) it seems like you're looking at a many-many relationship. As in, one territory can have many countries while at the same time, one country can have many territories

Hope that didn't sound jebrish lol

 

[Database Challenged]

Thanks, g37Sam.

 

[G37Sam]

Let me know if you need any more help