Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2017
    Posts
    4

    Unanswered: Lookups - Table vs. List

    Hello I am new here! I have some knowledge about relational databases and have created many in the past with Lotus Approach, but am trying to teach myself Access now, as it is what I have to work with here. So I wondered if it is better to using a table vs. a list so that when it comes to creating queries and reports I won't have any hiccups. I have read a lot of stuff in the past few weeks and I think I've seen someone on another forum imply that it is better to have tables. I just want to make sure I do the best thing from the beginning rather than have issues and have to fix something major.

    I do understand that a list is okay if you are not going to be adding/changing the values often, whereas, a table is preferred if you may need to update/add values more frequently.

    Basically, I have a table for Consumers where I want to enter the South Carolina county in which they live. Later I will need to have a query that lets me know what percentage live in each county. Of course, this would not be something that will ever have changes or updates to it since the state of SC has 50 counties and most likely won't have a new one!

    I also have several other fields that will need to have data choices, such as Title (Mr., Mrs., Miss, Dr., Ms., etc.); Age Range (17 and under, 18-24, 25-34, etc.); Status (Closed - Consumer Satisfied, Closed - Unsatisfied, Open, etc.). The Title field values most likely won't change, however, the others may change or need updating at some point.

    I appreciate any advice y'all may have!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    629
    Provided Answers: 33
    make a table for all your codes, Titles: mr,ms, etc,
    states: NY, WV, SC
    status'
    etc

    whenever you need a conversion ,include the table in your query.
    (you can get the count table, all states and their counties from the post office download)
    or google it.

  3. #3
    Join Date
    Mar 2017
    Posts
    4

    Thank you!

    Quote Originally Posted by ranman256 View Post
    make a table for all your codes, Titles: mr,ms, etc,
    states: NY, WV, SC
    status'
    etc

    whenever you need a conversion ,include the table in your query.
    (you can get the count table, all states and their counties from the post office download)
    or google it.


    Thank you!

  4. #4
    Join Date
    Mar 2017
    Posts
    4

    Lookup tables - one more question

    Would I need to include a field for "Title" or "County" in the table to which I will be joining the Lookup table that includes that lookup data? For instance I have a ConsumerInfo table, which has first name, last name, address, city, etc. and I will need to join that to the "Title", "State", "County", "AgeRange" tables, which each contain the pertinent lookup values.

  5. #5
    Join Date
    Mar 2017
    Posts
    4

    Lookup tables - one more question

    Quote Originally Posted by AmandaB View Post
    Would I need to include a field for "Title" or "County" in the table to which I will be joining the Lookup table that includes that lookup data? For instance I have a ConsumerInfo table, which has first name, last name, address, city, etc. and I will need to join that to the "Title", "State", "County", "AgeRange" tables, which each contain the pertinent lookup values.
    Got it! I answered my own question...yay! But I do have one more (famous last words) question for now . If I want the County field in the "ConsumerInfo" table to be indexed, as "Yes (Duplicates OK). I would only need to specify that in the "ConsumerInfo" table, correct? Or would I need to also do that in the "County table"? The reason I would like 'Yes (Duplicates OK)' is because obviously I can have many consumers from the same county.

    Please let me know if I am thinking of this correctly.

    Thank you!

Tags for this Thread

Posting Permissions

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