I have attached a Word 2016 document that outlines my new database that takes Implementation through Step One. With so much introductory information needed I felt it would be difficult to describe my problem and objective here.
Thanks to all that participates. I wish I could distribute points like we us to do but it appears that is no longer an option. Logic-for-Excel-Solutions.docx
Start by creating dummy tables having only the necessary columns to create the relationship diagram and the relationships. Post a screenshot of it. Also post the context in the text instead of a word document. Reading extra documents is tedious.
From the general problem and the sample, I'm not sure whether your topic sample makes sense. Cause a single work sounds more like a tag. So maybe it's
Tags -1:n-> SubTopics -1:n-> Issues -m:n-> Solutions
Tags <-n:1- Topics -1:n-> Issues -m:n-> Solutions
You mention cascaded deletes: I would not use them in this case.
For your question 1): The Topic table is no correctly implemented.
a) Your object naming could be better. Using general prefixes for tables and views makes no sense, cause both are data sources, thus the same class of objects from the consumer. Using prefixes which indicate the consumer, like frm_Topics for the view which collects the data for the Topcis main form or rpt_Topics for a topics report.
Column names should reflect the attribute. The column Topic should be TopicName or TopicText depending on the kind of attribute stored here.
b) The TopicName must have Allow Zero Length: No and you need to make it unique, thus create an unique index on that column alone. It must not be part of a multi-column primary key, cause in combination with an auto-incrementent, this would allow duplicate content in the TopicName column.
c) Having such a unique TopicName column solves a part of your problem automatically, cause Access will throw an error when a duplicate is tried to be inserted. You catch this error and show then your topic already exists message.
d) Catching different names representing the same as "PivotChart" or "Pivot Charts" requires a keyword table. You then use this keyword table to identify similar topics. The problem is: You must populate that table before any topic is entered.
e) The topic form should have a text box to search for topics and sub-form showing the topics with some stats, like number of sub topics, issues and solutions.