Results 1 to 3 of 3
  1. #1
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39

    Setting up table for nested relationships so that all family members appear in the table

    I am trying to set up a database where there are relationships: Project --> Assignment --> Task --> Item so each table has a field that relates to the record for the respective parent. I'd like to have it so eventually when navigating, selecting a Project will narrow down the assignments, etc. I'd also like to be able to see the name of the Project,Assignment, and task in the table. Is there a good resource for this situation? Thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    database tables need to be normalised - google to find out more but the principle is data is only stored once.

    From your description you need 4 tables

    tblProjects
    ProjectPK autonumber
    ProjectName text

    tblAssignments
    AssignmentPK autonumber
    AssignmentName text
    ProjectFK number - links back to tblProjects

    tblTasks
    TaskPK autonumber
    TaskName text
    AssignmentFK number - links back to tblAssignments

    tblItems

    itemPK autonumber
    itemName text
    taskFK number - links back to tbltasks

    I'd also like to be able to see the name of the Project,Assignment, and task in the table
    not a table, you use a query and/or form to do this

    However your requirement description probably does not go far enough. For example perhaps items is a relatively fixed number some of which might be allocated to more than one assignment. If this is the case you need a joining table and move the taskFK field to that table

    tblJoinTaskItems
    TaskItemsPK autonumber
    taskFK number - links back to tbltasks
    itemFK number - links back to tblItems

    and now tblItems is just
    tblItems
    itemPK autonumber
    itemName text

    clearly you may need more fields for each of the tables, and things get more complex when dates become involved e.g. an item is to be used on this task from 1/1/20 to 31/1/20. Or you need some sort of order - such as this assignment must be completed before the next assignment can be started

    If you are just starting out, learn to use some basic database design principles
    1. do not use spaces or non alphanumeric characters in table and field names -can generate misleading error messages
    2. do not use reserved words as table or field names - also can generate misleading error messages or fail to run - google 'access reserved words' to find out more. Common ones include date, desc, description
    3. Use meaningful field names in the context of the whole app, not just the table (e.g. don't use ID use taskID)
    4. do not use formatting, lookups or multivalue fields in table design - they will cause you much confusion over time. Tables are for storing data in its raw form, not for displaying it in a formatted way. If you have to use format, ensure the format includes all possible variations of the data - e.g. dates should show the time element, decimals a sufficient number of decimals, etc

    Edit: FYI PK =PrimaryKey, FK=ForeignKey or FamilyKey. My style is to use these rather than ID because a) it tells me which end of a relationship the field is and b) it does not get confused where xxID is a valid name for a field such as NationalInsuranceID




  3. #3
    pharmacologist is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    39
    Very helpful, thank you!!

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

Similar Threads

  1. Replies: 12
    Last Post: 07-24-2018, 06:01 AM
  2. Replies: 9
    Last Post: 11-22-2017, 09:13 AM
  3. Access 2013 Show Family Relationships
    By RevBlueJeans in forum Access
    Replies: 3
    Last Post: 03-08-2015, 08:19 PM
  4. Replies: 5
    Last Post: 01-31-2015, 02:44 PM
  5. All family members with one address
    By Grams in forum Access
    Replies: 5
    Last Post: 08-25-2014, 05:51 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