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