How do I design a decision tree?

[Seth Schrock]

This is a very broad question so all I'm looking for is a few hints of where to start. I need to make a decision tree, but I can't find anything online about how to do that in Access.

I'm trying to make it so that a police officer can determine the exact crime committed by answering some yes/no questions. For example, the crime Driving While Suspended would be the top of the decision tree. The next step would be Has the driver had prior convictions for this crime. Based on the answer to this question, the crime can be either a Class A Misdemeanor or a Class A Felony. This particular decision tree only has one set of questions, but my understanding is that there are other crimes that have more questions depending on the answer given to the first question. I have no idea how to start. I would like to have it so that there is no limit to the number of questions asked as I have no idea what the maximum number would be. Would a tree view control be the thing for this? I know TheSmileyCoder has done a very nice tutorial on this which I could use to help me, but I don't know if this is the way to go.

[zmbd]

Seth,

What you are asking for is nothing more than a logic flow chart.

You can implement these in a thousand different ways depending on the complexity of the situation.

What I have is a table... and then I steal from the biologist!
Tbl_kingdom
Tbl_phylum
Tbl_class
Tbl_family]

down to:
tbl_Species
[Species_pk] autonumber
[Species_question] text(250)
[Species_fk_genus] long
[Species_fk_actiontotake] as long

Each of the fields "Species_fk...." is a foreign key to a table of the that name, where in the the FK cascades thru (for example):
tbl_kingdom
[kingdom_pk] autonumber
[kingom_question] text(250)

tbl_phylum
[phylum_pk] autnumber
[phylum_question] text(250)
[phylum_fk_kingdom] long

tbl_class
[class_pk] autonumber
[class_question] text(250)
[class_fk_phylum] long

I only use as many tables a needed...
For a simple logic... maybe just family, genus, species
Always start with genus and species.
One can always add an upper level.

I've yet to find in 20-questions (is it animal, vegetable, or mineral) type logic trees a need for more than the entire thing
(Kingdom: Civil, Criminal)
(Phylum: next classes...)

In the form I've done this a few ways...
Cascading combo boxes (there's an insight article about this).
I have had an occasion where the user didn't like all of the combo boxes and wanted a parent/child type setup...
In that case I used "TABS" with late recordset binding. The first tab is bound to the top level table. When the user double clicks a record in the tab, the tab is "locked" to that choice, the next tab is made visible and it's record set is bound and filtered based on the prior tab, and so forth.
Once the final species tab is set, then I open a form or report based on the action specified.

Then there was the "rolling record set" nightmare... sigh.
The parent form with child.
Initial record sets were the top level table for the parent and the next level for the child.
Make the selection in the parent, then the child, then the child's record set was sent "up" to the parent from, and then the next level down is adopted to be the new child form's record set... the VBA behind this was brutal for me to implement... and when the gentlemen left... I moved this to the late-binding tab form.

How you get the records and subrecords and so forth... in Chemistry, there's usually a method or classification system in place and I follow that. I would hope that there is something similar for your project. Otherwise, time to list all of your final (species) questions that lead to an action and then sort those by type (genus), see if those have a relationship (family) and so forth.

[NeoPa]

I cannot comment on the TreeView control approach, as I know little about that (them).

However, what I would do is build a table of offenses and a table of questions, then treat the two as joined in a many-to-many (M:N) link by building a JOIN table between the two which includes a reference to the offense, a reference to the question, and some information as to what type of answer indicates the offense should be thus identified. One question should have links to all the offenses. This will always be the first question asked.

For instance, a Yes/No question would simply be identified as a simple Yes or No, whereas a count of existing offenses may require a particular value above which this offense is triggered. A multiple choice string value would require one of the valid choices, which could be of any data format. The value stored must match the type of question it's associated with.

The first question asked should be one that all offenses have a value for. From the answer to the first question you go through the loop until only one offense is left :

1. Find the question that is required for the most number of offenses that are still possible and that hasn't already been asked. The first time through the loop should always find the same question which is common to all offenses.
2. Filter the offenses by the result of that question.
3. If the number of offenses left is still multiple then loop back to step #1.
4. Unless the data has been entered incorrectly you should have a single valid offense remaining. Bingo.

This approach gives almost unlimited flexibility as well as the ability to upgrade and change at will. It does, however, rely a fair bit on code logic rather than simple SQL and data structure.

[ADezii]

In my opinion, the TreeView Control would be a perfect candidate for this scenario, and it would graphically display all the possible outcomes based on the prior Path(s) taken. Nodes would be expanded/collapsed as the need arises along with a large number of Sub-Levels.

[Seth Schrock]

Thankfully, I don't have to have the database count if there are prior records. The officer has to look it up on their system and they will just answer yes or no if there were past offenses. Also, if I'm understanding Z's and NeoPa's responses, the questions would need to be similar for the all of the crimes (ie first question is the species animal, vegetable or mineral), but I won't have that. Each crime will have its own particular set of questions. If some of the questions are the same, it is a coincidence. Similar to a file path on the computer. Each main crime would be a different drive letter and you would need to drill down through the folders (questions) until you find the file (the specific crime). Also similar to a file path, some folders might have the same name, but it is just a coincidence. That is why I thought of the tree view.

[zmbd]

You have to have a way to classify the questions.

Take a look at the following where in each subsection lead to to the next... let's take stalking:
Kingdom: http://www.law.cornell.edu/uscode/text/18
Phylum: http://www.law.cornell.edu/uscode/text/18/part-I
Class: http://www.law.cornell.edu/uscode/te...I/chapter-110A
Family: http://www.law.cornell.edu/uscode/text/18/2261A
Genus: ... well there wasn't one for this site; however, insert next level
Species: ... once again you get more specific.

Take a look at a biological or chemical nomenclature chart... it flows much like the tree-view control with the branching.
IN that the same question might apply to more than one type of case would mean a simple modification to my system wherein the grandparent-fk, the parent-fk, are referenced and I use a question table against the child

tbl_species
[species_pk]
[species_fk_family] (grandparent table 1:m)
[species_fk_genus] (parent table 1:m)
[species_fk_standard_questions]

Tbl_standard_questions
[standard_questions_pk]
[standard_question_text]

I've only had to do this once! Conceivably, one could add the great-grandparent and so forth; however, that adds to the complexity.
I corrected the issue by adding the next higher grouping table after having gone thru all of the questions in each group several times.

In any case, you have to have a means of classification. Now I pulled on the USC code because I'm very familiar with it... note that I could have gotten away with one fewer table to get to the stalking; however, the extra tables allow you add criteria etc... You should have a codification of criminal code upon which to base the logic tree

[NeoPa]

Seth:
Also, if I'm understanding Z's and NeoPa's responses, the questions would need to be similar for the all of the crimes

Not at all Seth. The flexibility is there to have commonality of questions across all the crimes, just as well as to have a separate set for each type of crime, or anything in between. Nothing about the design restricts it to either end of the spectrum. Your data alone specifies what links to what. That is the beauty of a data-driven approach (such as this).

[Seth Schrock]

Okay. I think that I will stick with the tree view as it doesn't require a lot of code and doesn't require a bunch of tables either. I also have a tutorial to help me out which helps greatly. Here is the link if anyone wants to watch: Treeview in Access Part 1

[NeoPa]

I'm not going to argue with that choice Seth :-) If the TreeView controls do the business for you in a straightforward way, and that was your original thinking anyway, it seems an ideal choice.