Results 1 to 2 of 2
  1. #1
    bradjake8 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    1

    Exclamation Use or not Use Multivalued Lookups

    Hello, I am trying to find the easiest way to relate tables and fields in my database and am hoping that someone might be able to give me advice. The current example that I am struggling with is as follows:



    I have the following tables
    • Aircraft Types
    • Aircraft Tooling
    • Tooling Owners


    In this example, I have a Aircraft Tooling Table with over 1000 tools, an Aircraft Type Table with over 20 different aircraft types, and a Tooling Owner Table with a possible of 12 different owners.

    Each tool can be used on one aircraft type or several (This is the tool applicability), Each tool can also belong to multiple owners, or owners can own multiple tools.

    I know that this is the typical situation for a junction table, but that seems like it would take forever to make all of the entries, and be difficult to manage going forward. It seems like you would need a row for each different circumstance; therefore duplicating a ToolID over and over again to cross it to each aircraft type and owner.

    A multivalue lookup would seem to be the easiest way to do this but everyone says not to do this.

    Does anyone have any ideas that could help me?

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I think that is reality. If you want to associate tools and aircraft in a many-to-many relationship, there will be a lot of records.

    Do you really have to associate a specific-owned tool to aircraft or maybe just the model of tool?
    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: 5
    Last Post: 07-21-2012, 07:30 AM
  2. Problem with lookups
    By Beachbum in forum Database Design
    Replies: 1
    Last Post: 02-01-2012, 12:22 AM
  3. No Lookups in a Table
    By oleBucky in forum Database Design
    Replies: 9
    Last Post: 03-23-2011, 01:40 PM
  4. lookups and data selection help
    By benjammin in forum Access
    Replies: 3
    Last Post: 11-28-2010, 04:45 PM
  5. Access ADP & Lookups
    By sql_dan in forum Access
    Replies: 0
    Last Post: 06-09-2010, 04:25 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