Results 1 to 4 of 4
  1. #1
    Clifford86 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    24

    Table Design - Relationship Question

    Quick question please.

    My tblAddress table has City, State, & Zip. I am thinking about creating tables instead for them.




    tblState
    tblCity
    tblZip


    Then setup the relationships for them. What I am not sure about is what the relationships should be like. My goal is to be able to cut down on errors and time. So on my form, when the zipcode is entered it auto fills State & City.

    Should I say:


    tblZip one-to-one tblState one-to-many tblCity
    or
    tblState one-to-many tblZip one-to-many tblCity
    or
    What?



    I am not sure about this. All suggestions much appreciated. Thanks

    ---
    Sincerely,
    Clifford86

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,885
    I don't think I'd attempt to split them in to 3 tables. Problem is, a town can related to many zip codes, many codes to one state, but worse, the same town name can be in several states, thus all have different zips. Consider one table and create a unique index on 3 fields - Town, State and PostalCode. That way you can have as many Springfields as you want but the combination of state and code will make them unique. This would also cover of the possibility (?) that a state has 2 towns with the same name or spelling variation. Don't know if exists or not.

    Accuracy would then be achieved by a state combo. The town list is then filtered according to the state chosen. The codes are then filtered to whatever matches the state and towns selected. That's known as cascading combo boxes.

    That's my take, for what it's worth.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,007
    I agree. Let tZip be a lookup table to fill in State, & City.
    Even that is not 100% since 1 zip may cross cities, but user can still pick City from combo.
    No relationships needed.

  4. #4
    Mickjav is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Location
    Margate, Kent
    Posts
    100
    In the UK we use a postcode which will give use the town/city county road all we have to do then is select the house number to fill out a clients address.
    All open code examples and free projects are only available from:
    https://databasedreams.createaforum.com/index.php

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Table Design - Relationship Structure
    By djspod in forum Database Design
    Replies: 2
    Last Post: 03-09-2017, 10:43 AM
  2. Question on creating relationship in table design
    By darylnet in forum Programming
    Replies: 3
    Last Post: 03-08-2017, 08:41 AM
  3. Tricky design question to enforce a relationship
    By TABROCK in forum Database Design
    Replies: 2
    Last Post: 09-04-2014, 12:15 PM
  4. Relationship / Overall Design Question
    By nunzii in forum Database Design
    Replies: 2
    Last Post: 04-24-2013, 04:08 PM
  5. Table and Relationship Design
    By GrayWolf in forum Access
    Replies: 6
    Last Post: 01-31-2012, 02:04 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums