Tables, etc. issue with regions and countries

[dillonhh]

Hello, this is my first post and should not be a difficult one as I am still a learning novice:

I am using Access 2007

My database includes a Table of "providers" located around the world. Each provider obviously has its respective address. To select the country, there is a lookup pointing to a "countries" table.

The problem is that I would also like to have a "regions" table, region being just a general well-known region such as Yucatan Peninsula in Mexico. Therefore each provider may also have a region specified in addition to just City,State,Country like Cancun,Quintana Roo,Mexico. However, the many countries may have regions with the same names, therefore this table must also depend on countries. In other words, every regionID is made up of both a country lookup from the "countries" table and a region, like "Yucatan Peninsula".

I think this is the best way to do it so far, but now I am wondering how I would fill the region info in on the Providers" table. If i have a lookup for that regions field it would show each all of the regions, but like I said there may be regions with the same names in different countries. How could I fill this in without manually entering the RegionID from its own table. Eventually I obviously want a nice form to fill this in. One thing I was thinking of is if maybe after entering the country, the regions field could automatically narrow down to only those found in that already selected country. But then we would not want people filling in the region before the country.

I hope my problem is clear, just ask if not. Thanks very much, Dillon

 

[Premy]

Cities too, can have identical names across different countries or even in the same country. I think u shoud have 4 tables:
tblCountry
tblRegion
tblState
tblCity

tblRegion will have entries like:
"North East", which may be the North East Region of the US, Mexico or Brazil.
tblCity will have entries like:
"Paris", which may be Paris-France or Paris-Texas.
The important thing here is to have just one name, i.e. just 1 North East, just 1 Paris, etc.

u just pick country, region, state and city from each table for the provider's record at hand. tblProviders should have:
ProviderID
CountryID
RegionID
StateID
CityID

Just combine all these 5 fields into your queries to filter out any specific provider(s).

tblRegion, tblState and especially tblCity might get quite large, since they respectively will be holding Regions, States and Cities from all over the world, but nothing a good combobox won't be able to handle.

Your Provider Registration Form will have:
txtName for the provider's name
cboCountry for the provider's country
cboRegion ...
cboState ...
cboCity ...
txtAddress ...
and so on.
cboCountry is bound to tblProvider.CountryID
cboRegion is bound to tblProvider.Region ID
...
...

It's kind a late and I'm answering your post straight away, without thorough analysis, so I might be missing out something here, but I basically think that this is more or less the way to go. Let me know if it's impractical, for some reason, so we can check it out again.

Regards,
Premy

 

[dillonhh]

Hi Premy,

Thanks for the email! I really appreciate it.

The method you explained is what I have already implemented for tables like Countries. I had not yet set it up that way for cities and states because I am not sure how some of that works in various countries around the world, but that is not important at the moment.

For the regions, I understand what you meant very well, but that would mean that in the regions combo box we would be seeing all of the possible regions for all countries around the world, correct? This would be fine if the all of the regions were things like Northeast and Southwest or Mountains and Desert, but there are also going to be various regions that likely won't be named that way in other countries, such as the one I had mentioned Yucatan Peninsula for Mexico, or somewhere like Patagonia for Argentina. It would be no problem for the user to simply find the one they want from the drop down combo box, since in theory they should know what it is before they look for it, but I am looking for something that does it a little different.

Let me try to explain:
Right now in the regions table I have three fields (rather than just the two I have in countries) and they are RegionID, CountryID (related to the countries table, or related to the CountriesID field in the providers table), and txtRegion. Maybe doing it this way would allow something (and I don't know how to do this) that once selecting the Country in the providers table will automatically narrow down the regions to show only those that apply. So for example, I may select Argentina in the providers table, tab to the next field (Regions), and see only entries found in Argentina (like Patagonia, Northwest, etc.) rather than all of the regions in the entire world, which would include Yucatan Peninsula not even found in Argentina.

I suppose doing it this way, however, would mean that a region like Northwest would have an entry in the regions table for each and every country that it applies to, ie Mexico:Northwest, Argentina:Northwest, USA:Northwest, etc. But maybe you know of another way I can somewhere sort down the regions without having the three different fields for the regions table.

Hopefully, I have not been more confusing than clear. Thanks again for you response earlier. -D

 

[Premy]

Ok, the best approach would probably be something like this:
tblProvider: ProviderID, CountryID, RegionID, StateID, CityID, Name, Address etc...
tblCountry: CountryID, Name
tblRegion: RegionID, CountryID, Name
tblState: StateID, RegionID, CountryID, Name
tblCity: CityID, StateID, RegionID, CountryID, Name

The first field is AutoNumber in every table, the rest will have to be filled in explicitly. Once all the data is entered, you can set up your comboboxes to return just the relevant entries, for example, the user chooses Mexico in cboCountry, so cboRegion will just show the regions of Mexico. Next he chooses Yucatan in cboRegion and so cboState will just show the states in Yucatan, Mexico. Next he chooses DF in cboStates, which leaves cboCity with just the cities in DF, Yucatan, Mexico.
So now we need to set up the comboboxes.

The RowSource for cboCountry will have to be hard set and should obviously be: "Select tblCountry.CountryID, tblCountry.Name FROM tblCountry ORDER BY tblCountry.Name;". BoundColumn = 1, Number of columns = 2, column-widths = 0;4 (change 4 to the width u want).

Now we'll set up it's Afterupdate event, in order to determine cboRegion's RowSource
Sub cboCountry_Afterupdate()
Dim S as string
S = "Select tblRegion.RegionID, tblRegion.Name FROM tblRegion WHERE tblRegion.CountryID =" & Me.cboCountry
Me.cboRegion.RowSource = S
Me.cboState.RowSource = ""
Me.cboCity.RowSource = ""
Me.cboProvider.RowSource = ""
End Sub

Now to the next level:
Sub cboRegion_Afterupdate()
Dim S as string
S = "Select tblState.StateID, tblState.Name FROM tblState WHERE tblState.RegionID =" & Me.cboRegion
Me.cboState.RowSource = S
Me.cboCity.RowSource = ""
Me.cboProvider.RowSource = ""
End Sub

Down another level:
Sub cboSate_Afterupdate()
Dim S as string
S = "Select tblCity.CityID, tblCity.Name FROM tblCity WHERE tblCity.StateID =" & Me.cboState
Me.cboCity.RowSource = S
Me.cboProvider.RowSource = ""
End Sub

Now at last:
Sub cboCity_Afterupdate()
Dim S as string
S = "Select tblProvider.ProviderID, tblProvider.Name FROM tblProvider WHERE tblProvider.CityID =" & Me.cboCity
Me.cboProvider.RowSource = S
End Sub

U can make various combinations using this tecnhique, depending on your needs. You can, for example add the RowSource code for cboProvider in all the other combos, like this:

Sub cboCountry_Afterupdate()
Dim S as string
S = "Select tblRegion.RegionID, tblRegion.Name FROM tblRegion WHERE tblRegion.CountryID =" & Me.cboCountry
Me.cboRegion.RowSource = S
S = "Select tblProvider.ProviderID, tblProvider.Name FROM tblProvider WHERE tblProvider.CountryID =" & Me.cboCountry
Me.cboProvider.RowSource = S
Me.cboState.RowSource = ""
Me.cboCity.RowSource = ""
End Sub
This will cause cboProvider to show all Providers from Mexico. If u do this in cboRegion, cboProvider will show all providers from the chosen region, and so on.

U may also choose to populate all combos from cboCountry, like this:

Sub cboCountry_Afterupdate()
Dim S as string
S = "Select tblRegion.RegionID, tblRegion.Name FROM tblRegion WHERE tblRegion.CountryID =" & Me.cboCountry
Me.cboRegion.RowSource = S
S = "Select tblState.StateID, tblState.Name FROM tblState WHERE tblState.CountryID =" & Me.cboCountry
Me.cboState.RowSource = S
S = "Select tblCity.CityID, tblCity.Name FROM tblCity WHERE tblCity.CountryID =" & Me.cboCountry
Me.cboCity.RowSource = S
S = "Select tblProvider.ProviderID, tblProvider.Name FROM tblProvider WHERE tblProvider.CountryID =" & Me.cboCountry
Me.cboProvider.RowSource = S
End Sub

Hope u got enough to chew on for now. Any doubts let me know
regards,
Premy

 

[dillonhh]

Hi Premy, Thanks a lot for the response. That is just what I want, but I am still having some trouble figuring out where I want to put the AfterUpdate info. I have tried a few things but am running into problems. The combo box for countries is no problem, but I am struggling with the rest. Maybe you could help me start smaller and just update the regions after selecting the country and ignore the rest for starters.

Thanks again, Dillon

 

[dillonhh]

Ok Premy, Ignore my last post, I think I figured it out. Thanks very much for the help, I really appreciate it!

 

[Premy]

You're welcome... don't forget to hit my scale ;-).

Regards,
Premy