Results 1 to 12 of 12
  1. #1
    ajbridges is offline Novice
    Windows 11 Access 2016
    Join Date
    Mar 2023
    Posts
    5

    Need help with defining table relationships for 3 tiers of data

    Hello,



    I am pretty new to access, having used databases before but never made one myself, and Iím struggling a bit with the best way to define the relationships between tables.

    I have 3 tiers of information which Iíve put into 3 separate tables. The first set is a list of broad topics. The second set is a list of projects that relate to those topics. The last is a list of descriptors about what each project covered within the topic (I called these keywords). Mainly, I want my keywords to be filterable or searchable in order to pull up all the projects (and which topic they fall under) that contain those keywords.
    So far, I can link the projects and the keywords to the topic, but where I am stuck is how to link the keyword to the project, as the same project under different topics uses different keywords.

    My question is, is this a normal or realistic ask or am I way off base on how I am thinking about it? (If I am) How would one normally relate 3 tiers of data within access?

    As an example, here is how my data might look like if I were to put it into bullet points. As you can see, sometimes a project will relate to multiple topics, and keywords can relate to multiple projects either under the same topic or under different topics.

    Topic #1

    • Project #1
      • Keyword #1
      • Keyword #2
      • Keyword #3

    • Project #2
      • Keyword #2
      • Keyword #3
      • Keyword #4
      • Keyword #5

    Topic #2

    • Project #1
      • Keyword #6
      • Keyword #7
      • Keyword #8

    • Project #3
      • Keyword #7
      • Keyword #9
      • Keyword #10

    Topic #3

    • Project #4
      • Keyword #5
      • Keyword #8
      • Keyword #11

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,979
    One way- topics in one table, projects in another, keywords in a third. Since projects/keywords looks like a many to many relationship, then a junction table would be required:
    tblProjKeywords
    ProjKwID_pk
    ProjID_fk
    KeywordID
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ajbridges is offline Novice
    Windows 11 Access 2016
    Join Date
    Mar 2023
    Posts
    5
    Thanks, Micron

    I think the junction table was the missing piece that I needed. I'm not sure I can fully visualize how everything should be laid out in my head yet, but I will play around creating all the tables and relationships and see if that clarifies things. I might have some more specific questions as I go.

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,979
    Maybe these will help
    Normalization Parts I, II, III, IV, and V
    http://rogersaccessblog.blogspot.com...on-part-i.html
    and/or
    http://holowczak.com/database-normalization/
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,234
    Hi

    You would need the following tables.
    Attached Thumbnails Attached Thumbnails Relationships.png  

  6. #6
    ajbridges is offline Novice
    Windows 11 Access 2016
    Join Date
    Mar 2023
    Posts
    5
    Hi mike,

    Thanks! That's what I ended up with, so I'm glad to know I'm on the right track!

    I am still having trouble determining the best way to make a form to be able to add data. It seems every time I try to add a new record, I'm always missing the spot for that record in another table.

    (Say I make a Topic Main form with subforms to show me which projects/keywords are related to that topic. I can see everything just fine, but if I try to add a new project, I get an error message that the record on tblProjects doesn't exist. I COULD add that record directly to the projects table, but then I would have to manually update the two junction tables to show which topics and keywords are related to that project).

    Is this an issue with the way the relationships are set up or can this be fixed with a well designed form?

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,979
    I read that as you're trying to add a child record when there is no parent record for it. That is not possible.
    If you're trying to add another child record (project?) to an already existing main form record, then there is another issue. You post could also be interpreted as having more than one subform on the main form. Consider compacting/copying and zipping db copy and posting here if you have gotten as far as creating forms.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,234
    Hi AJ

    See the attached example
    Attached Files Attached Files

  9. #9
    ajbridges is offline Novice
    Windows 11 Access 2016
    Join Date
    Mar 2023
    Posts
    5
    Quote Originally Posted by Micron View Post
    I read that as you're trying to add a child record when there is no parent record for it. That is not possible.
    If you're trying to add another child record (project?) to an already existing main form record, then there is another issue. You post could also be interpreted as having more than one subform on the main form. Consider compacting/copying and zipping db copy and posting here if you have gotten as far as creating forms.
    Hi Micron,

    I guess what I was saying was that I wanted to be able to add a new topic, or a new project, or a new keyword, from one location instead of having to open up the individual tables. Having really no experience here, I don't know what sort of possibilities exist to be able to do that. I've since deleted any forms I made because they were clearly very broken. (Really all I tried was adding different combinations of fields from the form wizard to see if I could make it work). My tables and relationships look exactly as the diagram that Mike posted.

    Another issue is that once I add the data, I then need to be able to say how it relates to each other, which requires entries in the two junction tables. The form that Mike made seems to be able to do this step, which is great! I just can't define a new topic or a new project or a new keyword from that form, only choose from the lists of existing ones.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,234
    Hi AJ

    See the attached.
    You can now add a Topic, Project & Keyword
    Attached Files Attached Files

  11. #11
    ajbridges is offline Novice
    Windows 11 Access 2016
    Join Date
    Mar 2023
    Posts
    5
    Thanks Micron and Mike,

    I am going to mark this thread as solved. I was able to take a look at the resources Micron linked and the database Mike made to help me understand how to get where I need to go and be able to build/edit forms in the future!

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,234
    We were Glad to help

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

Similar Threads

  1. Defining Relationships in Access
    By Marier in forum Access
    Replies: 2
    Last Post: 11-29-2018, 09:24 AM
  2. job costing, multiple tiers of validation
    By St.Alphonzo in forum Database Design
    Replies: 2
    Last Post: 02-24-2017, 08:30 AM
  3. Defining an Array in an Access Table
    By EddieN1 in forum Database Design
    Replies: 9
    Last Post: 10-10-2014, 08:09 AM
  4. Replies: 22
    Last Post: 05-22-2014, 12:25 PM
  5. Replies: 4
    Last Post: 05-07-2013, 11:14 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