Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2015
    Posts
    9

    Unanswered: Best way to track geographies that apply to a record

    Hello,

    I should let you know up front that I'm fairly new to access and learning as I go. I've used some basic VBA with success, but I'm hoping to limit the coding if possible.

    The project I'm working on is a database that tracks a wide variety of research sources. i.e. court records, business information sources, media archives, etc. My goal is to have each record contain the geography that the source covers (City, State/Province, Country), where the user selects State or Country from a predetermined list. The problem I'm running into is that a source may cover multiple states or countries. I have looked at using multi-value fields, but the consensus seems to be that this is poor database design. I'm wondering if you all have thoughts on what the best way to approach this would be.

    Should I store all of the countries in one field or multiple? What is a user friendly way to input that data?

    I realize this is a general question. Any ideas on how I should go about this are appreciated. I can post my database if it would help.

    Also, I am using Access 2007.

    Thanks,

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Refrain from using multi-value fields. That's not only poor database design, that's no database design at all as that violates the normal forms which are the foundation on which the concept of relational database is built (see: http://www.bkent.net/Doc/simple5.htm, http://www.studytonight.com/dbms/dat...malization.php or https://en.wikipedia.org/wiki/Database_normalization). The "normal" way to associate several rows in one table to several rows in another table (many-to-many relationship) uses a junction table. See: http://www.tomjewett.com/dbdesign/db...e=manymany.php and http://en.tekstenuitleg.net/articles...ship-in-access.
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    require users to specify countries
    and states as separate entities

    you can use the ISO coutnry codes (Either 2 or 3 digit versions)
    beware of using country specific terminology in desioging the db and or the forms

    eg
    the US has states as a major subdivision, Canada has provinces, the UK has counties

    the US has ZIP Code which has little or no geographic meaning (especailly int he exceptions to the overall theory), the UK has a very specific geographical meaning. A UK postcode ties an address to a very limited area.

    be carefull of meaning. a ZIP code is specific tot he states, the UK & Canada use Postcodes, France uses Cedex... essentailly ther are all the same thing, but it will pee people off referring to coutnry specific terminology and or styles. dont' even go anywhere near phone numbering styles
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •