Results 1 to 6 of 6
  1. #1
    qvqv is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    18

    Lookup Table

    Hi



    A look-up table generally has an ID field with a primary key and a value field.


    I only want to use the value field, which will be a Primary Key.




    Is there anything wrong with having only one field in the table, being used as a primary key and a look-up filed from another table?


    Many thanks in anticipation

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No. Just probably uses more storage.

    Usually advise not to build lookups in table but since you are not building lookup with alias, go ahead.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    What if that value can can change?

    Eg 'Date Expected' becomes 'Date Promised'?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A lookup table is a table of values, usually for a combo or list box. @June7, I think you are confusing that with a lookup field, which I don't think is the subject.
    So the question as I interpret it: is it OK to use meaningful data as a primary key. Yes but it's usually not recommended, one reason being what Welshgasman says.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There is some confusion as to your intent. Can you give us a description and/or an example to help clarify?

    I only want to use the value field, which will be a Primary Key
    .

    A lookup table, often used as a reference table, that contains some authoritative value(s) than can be used in different contexts.

    Example: ISO Country Codes, ISO Language Codes

    All of these could use the ISO_Country table for values.
    CustomerCountry
    ShipperCountry
    ExporterCountry

    In this example, the various tables would use the CountryCode as a foreign key to obtain the country name when/where needed.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    No, I am not confused, just maybe poorly phrased reply.

    There is no technical problem having a single field table and using that table as source to build combobox or listbox.

    However, by not using an autonumber as primary key and saving that value, will likely use more storage in the other table. This may or may not be a concern.

    If there are no other fields in 'lookup' table then it is irrelevant whether that table has designated primary key as there will be no need to even link to table in queries.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 9
    Last Post: 10-10-2019, 05:13 PM
  2. Replies: 3
    Last Post: 06-15-2019, 08:51 AM
  3. Replies: 3
    Last Post: 06-02-2017, 03:49 PM
  4. Replies: 5
    Last Post: 06-25-2014, 09:19 AM
  5. Replies: 8
    Last Post: 03-22-2012, 08:48 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