Have you ever used one of those plant identification guides? I'm attempting to make some decsion logic much like that. It involves many many queries, so I've decided to save them in a table. The table has:
Code:
pk: ID (autonumber)
QueryDescription (text)
SQLTest (memo)
NextStepTrue (Number)
NextStepFalse
End (Boolean)
The evaluation program reads has an ID in mind for the top level, and is recursive using the [NextStep] for the later levels. It opens an recordset, queries using the SQLText and then calls the appropriate NextStep unless End is true. Is there a better way to do this?
To expand a little on what baldeagle is talking about, plants and other organisms are classified into hierarchical groups (taxa) like kingdom, phylum, order, family, genus, and species.
To identify a particular specimen biologists have developed dichotomous 'keys'. These keys are a series of questions that usually ask questions about the organism's anatomy that intitially place the specimen into a very broad group, and with each subsequent question, narrows down which group the specimen belongs to. Ideally, the key terminates when you reach the species level. However, some keys may only identify down to family or genus.
As an example
Q1. Specimen is multicellular?
A. Yes (Kindom Plantae, Kingdom Animalia, Kingdom Protista, Kingdom Fungi) Goto Question 6
B. No (Kindom Monera). Goto Question 2.
Q2. Specimen contains cellular organelles such as mitochondria etc.
A. Yes (Archaebacteria, Eubacteria) Goto Question 3.
B. No (Viruses, Viroids). Goto Question 4.
And so on.
I'm not sure I understand what function the SQLTest field serves, baldeagle. The way I would apprach it would be slightly different.
I'd set up two global variables. One called globNextStep (int) and one called globFinal (boolean).
Have a form (fmQuestion bound to a QuestionTaxa table) that shows a question with two command (answer) buttons. Make the user click on one of the two answer buttons to close the form. When they click it check if this record has KeyEnd = True (End is a reserved word in Access so you should not use it as a field name). If KeyEnd is true then set globFinal = True and close the form. If KeyEnd is not true then we need to go to the next question. Set the value of globNextStep to the relevant id based on which button the user clicked and close the form.
You might use something like (air code)
tbl QuestionTaxa
QuestionTaxaID (pk, autonumber)
QueryDescription (text)
NextStepTrue (Integer)
NextStepFalse (Integer)
KeyEnd (Boolean)
Taxa (Text) 'This is the taxon returned at the terminus of the key
Code:
Public globNextStep as Integer
Public globFinal as Boolean
Public function GetglobNextStep() as Integer
GetglobNextStep = globNextStep
End Function
Public Function RunKey() as String
globFinal = False
globNextStep = 1 'or whatever the QuestionID of the first question is
Do While globFinal = False
DoCmd.OpenForm "fmQuestion", , ,"[QuestionID] = " & GetglobNextStep(), , acDialog
Loop
RunKey = Nz(Dlookup("Taxa","QuestionTaxa","[QuestionTaxaID]=" & GetglobNextStep()),"Sorry. No Taxa in the table!")
End Function
Then, to run the key, you'd call RunKey() (perhaps from the click event of a command button), something like
Code:
Private Sub Command1_OnClick()
dim mytaxa as string
mytaxa = RunKey()
Msgbox "The result was: " & mytaxa, vbinformation
End Sub
Forgive me if I am an ignornmaus, but wouldn't it make sense to have tables as thus:
(This may be more appropriate for taxa than baldeagle's plant description, but I hope he'll get the idea and am also assuming one question is only needed for each level- if it's more complicated, questions should be in its own table)
tblKingdom
KingdomID
KingdomName
KQuestion
tblPhylum
PhylumID
KingdomID 'Foreign key to Kingdom
PhylumName
PQuestion
tblOrder
OrderID
PhylumID 'Foreign Key to Phylum
OrderName
OQuestion
....
tblSpecies
SpeciesID
GenusID
SpeciesName
Then in a form, you would just need seven combobox to select the appropriate response to each question and once they're filled, type in the plant's name and you're done?
No....reason being that there are zillions of taxonomic hierarchical levels and not all of them are used consistently.
For example, you can have
Kingdom
Superclass
Class
Subclass
SuperPhylum
Phylum
SubPhylum
SuperOrder
Order
SubOrder
InfraOrder
Superfamily
Family
Subfamily
Genus
Species
Variety
(there are more intermediates too but off the top of my head it get tricky to remember)
For some groups of critters, no information exists for some of the levels, whereas for others it does. It's an artifact of the ad-hoc way that taxonomy has developed over the years. As biologists discover that group X really are two different groups they invented new taxonomic level to deal with the increasing complexity that they were discovering. And sometimes a quaetion in a key might not actually take you down to the next level in the tree, but rather subdivide a group into two parts then ask a followup question that is specific to one or the other parts of the original group.
Unfortunately, a recursive relationship really is the most suitable method for dealing with this IMO.
Ah. Goes to show you how much the textbooks in school *doesn't* tell you. In the book, it sounds as if it was all neat and tidy with seven level of classification.
If baldeagle's problem is anything similar to taxa, it looks to me that we may have some missing entity (e.g. a plant with undefined family but defined order), correct?
So, something like this?
tblSpecimen
SpecimenID
SpecimenName
...Details about this specimen BaldEagle wants to know about
tblClassification
TaxaID (Looks up a table of taxa with all levels)
SpecimenID
tblTaxa
TaxaID
TaxaName
TaxaPrecedence
Then in a form, we can get a specimen's complete classification by working from top-level taxa (TaxaPrecedence=1) then downward to the bottom level, with flags of Undefined or whatever for missing levels....
Well, usually the traditional levels are filled (order, family, genus etc) but some of the intermediates are filled variably.
That's for the classification system itself. Then keys add a new wrinkle by only identifying groups to a certain level (sometimes to species, sometimes only to family etc).
I'm not sure what TaxaPrecedence represents? Is this a numeric equivalent to hierarchy level/nodes, or somesuch?
If so, I'm dubious about it. Taxonomy is usually depicted in tree view. The number of nodes you encounter between Kindom and Species/Variety) depends on which branch of the tree you go down. Some families have a sub family, some do not. The only thing you can say for sure about a taxa (child) is which taxa is the parent. It's analogous to trying to trace ancestral family trees.
If you're wanting to build the completer classification I think you have to start with the most specific taxa, then work your way back to the ultimate parent taxa (kingdom) rather than starting with the broadest and working your way down.
