I'm using a similar setup with states and coutries only. I don't use a city
lookup table which is what I use these for on forms only.

tblState
[StateID] * Number
[State] Abbreviation
[State Name] Full Name of state
[CountryID] Number


tblCountry
[CountryID]* Number
[Country Name] Full name of country


Bernard Piette wrote:
With the help of Allen Brown we've come up with this idea.

Here's a simpler suggestion that identifies country, state/province +
country, and city + state/province + country:

tblCountry
*CountryID* Text abbreviated name
Country Text full name.

tblState
*StateID* Text abbreviated name
*CountryID* Text f.k. to tblCountry.CountryID.
State Text full name.

tblCity
*CityID* AutoNum primary key
City Text
StateID Text ) f.k. to tblState
CountryID Text )

Primary keys are marked with asterisks. I've suggested using natural
keys for the first 2 tables rather than autonumbers. An artificial
key seems like unnecessary overhead to me, and this also solves the
interface issue of the disappearing values when the bound column is
hidden and you filter your combos.

You could a natural key in the 3rd table as well: City + StateID +
CountryID. That would work well, you may well *want* that combination
in your related tables. However the 3-field key can start to get
unweildy if you then have other tables related off that that become
4- and 5-field keys. So, while you certainly want a unique index on
the combination of those 3 fields, I've suggested the artificial key
as I imagine that all sales data end up relating back to the CityID
at some point.

Question IS ? ...

Do i really need an country ID since there is a known 255* or so
limit and by default no two countries will EVER EVER have the same
name, do I reallly need to have CountryID, won't Country alone
suffice...

The reason I ask leads to my second question,
When in my other tables i create fields for let's say Country or
province my programmer wants me to have the lookup store the names
not the IDs, is that correct, and if I do it that way won't all my
reports show country 33 for Canada vs showing the actual name of
canada.

What should I be storing in my lookups fields for each of Country
Province City in bout 10 tables the ID "33" or the name "quebec" for
example...

Or... should I be leaving these fields as text in the tables and use
lookups only in my forms for said fields of Country State and City
which I find in about 10 or 15 tables and subsequent forms...

While a little longwinded I just wanted to get my question across..

Thanks for all upcoming answers.

Bernard Piette

--

Joe Cilinceon