Looking up Cities, States, & Countries

[Guest]

In Excel I have lookup formulas for determining if a city/state combination
is considered in a high drug traffiic area or high financial crimes area. I
also have a lookup formula for determining if a country is considered high
risk from a money laundering or terrorist funding standpoint.

The city/state lookup formula utilizes over 50 sheets (one for each
state/possession) and the formula uses a city cell and a state cell in the
master sheet and goes to the applicable state sheet and looks to see if the
city is listed. If so it returns TRUE.

The country lookup is simpler. I have a list of all of the current high risk
countries and if the country shown in the master sheet is on that list it
returns TRUE.

Now I need to duplicate those lookups in Access. When a city and state have
been entered I need to determine if that combination is on the "list". I need
to do the same thing with countries.

I am able in the workbook to "spell check" the country names and would like
to do that in Access. In Excel I have a list of over 300 countries (including
multiple ways of entering them) and check that list against what the user has
entered to see if it is on the list. It's not foolproof but helpful. If the
country name is not found it allows it to be entered and the high risk lookup
still works.

Your suggestions on how I can accomplish these lookups in Access will be
most appreciated. Unfortunately I am not a programmer and get most of the
code I need from with these discussion groups.

Thank you,
David Vollmer

 

[Marshall Barton]

In Excel I have lookup formulas for determining if a city/state combination
is considered in a high drug traffiic area or high financial crimes area. I
also have a lookup formula for determining if a country is considered high
risk from a money laundering or terrorist funding standpoint.

The city/state lookup formula utilizes over 50 sheets (one for each
state/possession) and the formula uses a city cell and a state cell in the
master sheet and goes to the applicable state sheet and looks to see if the
city is listed. If so it returns TRUE.

The country lookup is simpler. I have a list of all of the current high risk
countries and if the country shown in the master sheet is on that list it
returns TRUE.

Now I need to duplicate those lookups in Access. When a city and state have
been entered I need to determine if that combination is on the "list". I need
to do the same thing with countries.

I am able in the workbook to "spell check" the country names and would like
to do that in Access. In Excel I have a list of over 300 countries (including
multiple ways of entering them) and check that list against what the user has
entered to see if it is on the list. It's not foolproof but helpful. If the
country name is not found it allows it to be entered and the high risk lookup
still works.

In a database (Access or whatever), you use tables to store
the information and queries to retrieve it. The first thing
to note about designing your tables is that you really need
to follow the rules of Normalization, which can be vaguely
summarized as Except for Foreign Keys, each value can only
be in one field in one record in one table.

Translating all that means that you need several tables.

Countries:
ID AutoNumber (Primary Key)
PreferredName Text
Risk Integer

CountryAliases:
CountryID Long (Foreign Key to Countries)
AliasName Text

States:
StateID AutoNumber (Primary Key)
StateName Text
CountryID Long (Foreign Key to Countries)
Risk Integer

Cities:
CityID AutoNumber (Primary Key)
CityName Text
StateID Long (Foreign Key to States)
Risk Integer

With that done, you can then create forms to enter/edit the
data. Probably a main form for the Countries table with
linked subforms for the other tables. Most likely you will
want a separate search form where you can specify the
country/state/city pf interest and display the result,
probably just by using three dependent combo boxes.

 

[Guest]

Thank you, Marshall,

I am not sure how to create the three dependent combo boxes as I have tried
that before and couldn't get it to work correctly.

David

 

[Douglas J Steele]

Assume your 3 combos are named cboCountry, cboState and cboCity.

In the AfterUpdate event of cboCountry, put code to set the RowSource for
cboState:

Private Sub cboCountry_AfterUpdate

Me.cboState.RowSource = "SELECT State FROM States " & _
"WHERE Country = '" & Me.cboCountry & "'"

End Sub

In the AfterUpdate event of cboState, put code to set the RowSource for
cboCity:

Private Sub cboState_AfterUpdate

Me.cboState.RowSource = "SELECT City FROM Cities " & _
"WHERE Country = '" & Me.cboCountry & "'"
" AND State = '" & Me.cboState & "'"

End Sub

 

[Guest]

Thank you Douglas. I will try your suggestions and see if I can make it work.

David