Results 1 to 6 of 6
  1. #1
    kyjae is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Jun 2014
    Posts
    1

    Making a Lookup Table Choice dependent upon a value of another Lookup Table Choice

    Hi All,

    From the start, I must confess to being an Access newbie so please bare with me, and I may have even posted this is the wrong forum which is further eveidence of my newbieness, but I'm looking for pointers to solve a problem and learn something new as opposed to finding the complete solution. Also I'm from the UK so terms like 'County' might translate to 'State' if you're from the US.

    So here is the scenario:-

    I have four tables called

    Company_Details - contains comapny name, address fields, etc
    UK_Country - contains 3 countries of the UK i.e. England, Scotland, Wales.
    England_Counties - contains a list of all English counties in alphabetical order
    Scotland_Counties- contains a list of all Scottish counties in alphabetical order
    Wales_Counties- contains a list of all Welsh counties in alphabetical order


    In the 'Company_Details' table is a field called 'Country' which looks up the values from UK_Country table and gives the choices England, Scotland, Wales. There is another field in the 'Comapny_Details' table called 'County'. What I would like to achieve is that this lookup choices for 'County' is dependent upon the selection made from the 'Country' field i.e.

    If Country selected is England, The 'County' field shows only the list from the England_Counties table
    If Country selected is Scotland, The 'County' field shows only the list from the Scotland_Counties table
    If Country selected is Wales, The 'County' field shows only the list from the Wales_Counties table



    I'm sure Acees must allow this but I'd appreciate some advice on how to go about this from those more knowledgable and experienced than me.

    Many thanks.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What you are seeking is Cascading Comboboxes.

    free videos showing the technique.

    http://www.datapigtechnologies.com/f...combobox1.html
    http://www.datapigtechnologies.com/f...combobox2.html

    Good luck.

  3. #3
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    Just a suggestion.

    Why not combine your Counties tables (England_Counties, Scotland_Counties, and Wales_Counties) into one table.
    Use the Country and County and the key to look up in that combined table.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by lfpm062010 View Post
    Just a suggestion.

    Why not combine your Counties tables (England_Counties, Scotland_Counties, and Wales_Counties) into one table.
    Use the Country and County and the key to look up in that combined table.
    Like mentioned here.....

    A normalized data structure would be to have a table for Countries and another table for Counties. In the table for Counties you would store the Primary Key value of the relative record from the Country table.

  5. #5
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    It was not normalized to begin with. Like I said, it is just a suggestion to reduce from 4 tables to 2 tables.
    Of course, you can normalized like ItsMe says and re-construct all your tables and Primaly keys.

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    @ lfpm062010
    I was simply agreeing with your comment.

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

Similar Threads

  1. Replies: 3
    Last Post: 06-28-2013, 01:59 PM
  2. Replies: 7
    Last Post: 10-16-2012, 06:43 PM
  3. Replies: 6
    Last Post: 05-05-2012, 08:43 AM
  4. Replies: 8
    Last Post: 03-22-2012, 08:48 AM
  5. Using lookup data for table 1 mutible times in table 2
    By mbjazz in forum Database Design
    Replies: 5
    Last Post: 04-26-2011, 01:18 AM

Posting Permissions

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