Results 1 to 2 of 2
  1. #1
    seane is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2017
    Location
    Texas
    Posts
    1

    Database structure assistance


    Good day all. I apologize firstly as I am a novice to Access. I have dabbled in it in the past, but never retained much nor actively used it for a real world application.
    However, I am attempting to create a customer database and I KNOW access has the capability as a solution, I just haven't been able to find a starting point.

    I am looking for assistance in either pulling a template and customizing or building based on some guidance.

    My organizational issue is as follows:

    I have multiple customers and who have multiple assets that carry various equipment. The various equipment is the smallest of the variable sets.
    The dataset I have is as follows:

    Asset - 1
    • Owner
    • Operator
    • Manager
    • Equipment
      • A
      • B
      • C
      • D

    Asset - 2
    • Owner
    • Operator
    • Manager
    • Equipment
      • B
      • C


    An owner can be associated with multiple Assets but also be an operator/manager of multiple assets.
    Same goes for an operator and Manager; multiple assets and cross association

    What I would like do is be able to query any variable and pull a list of associated with with it.
    Ex. All assets with equipment B
    or All assets associated with Operator X

    I am sure I have seen a template similar to what I am describing but I am unable to locate it nor do I have the skill to create from scratch.
    Any assistance would be greatly appreciated!

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need primary and family keys and a many to many relationship table - structured something like this

    Person table

    • PersonPK
    • PersonName



    Asset table

    • AssetPK
    • OwnerFK - link to personPK
    • OperatorFK - link to personPK
    • ManagerFK - link to personPK


    Equipment table

    • EquipmentPK
    • EquipmentName

    Linking table


    • LinkPK
    • AssetFK
    • EquipmentFK

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

Similar Threads

  1. assistance with database to connect to excel frontend
    By stan101 in forum Database Design
    Replies: 1
    Last Post: 11-19-2016, 08:31 AM
  2. Replies: 3
    Last Post: 10-20-2016, 04:25 PM
  3. Urgent Access Database Assistance
    By ETCallHome in forum Access
    Replies: 4
    Last Post: 04-04-2014, 08:35 AM
  4. Database Design Assistance would be greatly appreciated
    By Zephyx in forum Database Design
    Replies: 4
    Last Post: 06-27-2013, 12:31 PM
  5. Need assistance for my inventory database
    By undee69 in forum Access
    Replies: 5
    Last Post: 12-19-2012, 10:13 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
  •  
Other Forums: Microsoft Office Forums