Validating process for new Access 2016 database

Frank Freese
Frank Freese used Ask the Experts™
on
Folks,
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
hmm, what are your questions?

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

instead of

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.

Author

Commented:
ste5an,
Thanks for comments. Let me try to answer them to the best of my ability.
1. WHAT ARE YOUR QUESTIONS?
Answer: I should have been clearer in regards to the specific problem in the first step in order to go forward. The first step is presented to determine what is the best method to validate that a Topic has not already been enter to avoid duplicate named Topics? Can this be done at the table level by making the field Topic a Primary Key? Or would it be better to search through the tblTopic. If the Topic already exist then no new Topic can be entered. Which approach serves to be the best solution or is there another approach that is better?
2.  From the general problem and the sample, I'm not sure whether your topic sample makes sense a single work sounds more like a tag.
Answer: First of all I was simply attempting to introduce a database design, subject to change, based upon the advice provided by the Expert-Engineer participants. Therefore, I started with the first table that serves to be the primary table from which the next table tblSubTopic will depend upon. You used the term "tag" which I hope you will clarify regarding the first table and subsequent tables.
3. You mention cascaded deletes: I would not use them in this case.
Answer: I elected Cascade delete because there may be a need to delete any SubTopic, Issue, and Solution without deleting the Topic. Cascade Delete may not be the best way to accomplish this so what approach should be used?
4. The Topic table is no correctly implemented.
Answer: The term implement was probably a poor word. What I was wanting to communicate was the design of this  table Topic correct? I have yet to show additional tables at this time so as not to confuse my objective which is "To create an Access Database for the purpose of recording various Excel Topics, SubTopics associated to a Topic, Issue associated to a SubTopic, and Solution associated to an Issue."
5. Your object naming could be better.
Answer: The Object Naming convention I chose was based upon following recommendations from other developers of which there can be as many naming conventions as developers. I chose to prefix tbl for tables, frm for forms, qry for query, rpt for report and mod for module.
 6. Column names should reflect the attribute. The column Topic should be TopicName or TopicText depending on the kind of attribute stored here.
Answer: I can see your logic and your recommendation will be implemented. Good catch!
7. 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, would allow duplicate content in the TopicName column.
Answer: I can see your logic and your recommendation will be implemented. Good catch!
8. 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.
Answer: Indeed, this is a problem. A keyword table would be cumbersome to maintain. How do you feel about a Message box that serves to  ask the user to confirm that a Topic entered was not located and they could accept or reject the Topic?
9. 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.
Answer: It appears that a text box searching topics needs to be implemented as stated in my answer to your question 8. I am having a problem regarding the second part of your question here regarding stats. However, I do see value in showing all SubTopics as it relates to the
Topic. This would allow the user to continue towards a Solution by selecting a SubTopic they are interested in.

Note: Items italicize and bold are questions back to you. Items in bold are your suggestions to be implemented.
Distinguished Expert 2017
Commented:
You can enter as many Topics as you want without proceeding to the next step
This is what I was trying to get you to tell me in your previous thread where you specified that all FOUR tables must have data before any was saved.

Having four nested subforms on on main form can get cluttered so I might implement this using popup form.  IF I put all the forms on one form, I would do them side by side.  Here's a rough example of how you might build such a form.  There are some non-obvious tricks involved.  Note how the master/child links are defined.  Also note the code in the current and BeforeInsert events.  The BeforeInsert code isn't required if you set the master/child links properly but I put it there in case you prefer to make these popups.  If the forms are not actually subforms, you MUST set the FK manually in the BeforeInsert event.  The code in the Current events forces the lower level subforms to reset themselves to link up with the parent form.
NestedSubforms.accdb
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
Pat, I saw my error in logic after I chewed on it. Last night I came to the SAME conclusion and design as you recommend! It seems great minds think a like. I'll play around with this at first light but accept your solution. Thanks

Author

Commented:
Thanks folks
Distinguished Expert 2017

Commented:
You're welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial