Solution - Experienced Challenge 8: Twenty Questions
So were we able to come up with a solution to challenge 8? Good question. As it turns out, we wrote the first half of our solution, then put it to the test, just to make sure everything was working correctly. When we did so, we discovered that not only was the code working, but the computer was able to guess the animal we were thinking of, too. (Yes, we know: spooky.) We tried it a few more times (10 or 11, to be a little more precise) and each time the computer was able to guess the animal. At that point we decided that our subroutine wasn’t half-finished after all; instead it was completely finished.
So does that mean that the subroutine is guaranteed to guess the correct animal each and every time you play the game? Well, we aren’t willing to go that far. But thanks in part to the fact that we have a relatively small number of animals in our database, it was good enough for us.
Note. If we really do have a relatively small number of animals in our database shouldn’t we have added more animals to that database? In answer to your question: no. After we committed ourselves to this challenge we only then went searching for a database of animal facts. When that search came up empty, that meant we had to create this database by hand. When you’re creating a database like this by hand, suddenly 71 animals seems like a huge amount.
Incidentally, if you’ve spent the past two weeks thinking, “Those OfficePalooza guys are so lazy; I bet they wouldn’t even create a database of animal facts by hand,” well, you were wrong. Not about us being lazy, mind you. But at least we did create a database of animal facts by hand.
So what solution did we come up, or half come up with? This one:
Private Sub Command8_Click()
Set objDatabase = CurrentDb()
Set objTable = objDatabase.CreateTableDef("WorkingTable")
objTable.Fields.Append objTable.CreateField("ID", dbText)
objTable.Fields.Append objTable.CreateField("Name", dbText)
objTable.Fields.Append objTable.CreateField("Type", dbText)
objTable.Fields.Append objTable.CreateField("Diet", dbText)
objTable.Fields.Append objTable.CreateField("Nocturnal", dbBoolean)
objTable.Fields.Append objTable.CreateField("Lifespan", dbText)
objTable.Fields.Append objTable.CreateField("Weight", dbDouble)
objTable.Fields.Append objTable.CreateField("Group", dbText)
objTable.Fields.Append objTable.CreateField("Protection", dbText)
objTable.Fields.Append objTable.CreateField("Gestation", dbDouble)
objDatabase.TableDefs.Append objTable
objDatabase.Execute ("INSERT INTO WorkingTable SELECT AnimalFacts.* FROM AnimalFacts;")
objDatabase.TableDefs("WorkingTable").Fields("Group").Name = "GroupName"
intAnswer = MsgBox("Is your animal nocturnal?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where Nocturnal = 0;"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where Nocturnal <> 0;"
End If
intAnswer = MsgBox("Does your animal have a group name?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where GroupName Is Null;"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where GroupName Is Not Null;"
End If
intAnswer = MsgBox("Is your animal threatened, endangered, or protected?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where Protection Is Null;"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where Protection Is Not Null;"
End If
intAnswer = MsgBox("Is your animal a carnivore?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where Diet <> 'Carnivore';"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where Diet = 'Carnivore'"
i = i + 1
intAnswer = MsgBox("Is your animal an omnivore?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where Diet <> 'Omnivore';"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where Diet = 'Omnivore';"
End If
End If
intAnswer = MsgBox("Is your animal a mammal?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where Type <> 'Mammal';"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where Type = 'Mammal';"
End If
intQuestionsLeft = 15 - i
Set objTempTable = objDatabase.OpenRecordset("WorkingTable", dbOpenDynaset)
intRecordCount = objTempTable.RecordCount
objTempTable.Close
If intRecordCount < intQuestionsLeft Then
Set objRecordset = objDatabase.OpenRecordset("WorkingTable")
objRecordset.MoveFirst
Do Until objRecordset.EOF
intAnswer = MsgBox("Is your animal the " & objRecordset.Fields("Name"), vbYesNo)
If intAnswer = vbYes Then
MsgBox "I win!"
objRecordset.Close
objDatabase.TableDefs.Delete "WorkingTable"
Exit Sub
End If
objRecordset.MoveNext
Loop
End If
MsgBox "I give up; you win!"
objRecordset.Close
objDatabase.TableDefs.Delete "WorkingTable"
objDatabase.Close
End Sub
As for how this code works, well, let’s see if we can figure that out. As you can see, we start out simple enough, using this line of code to create an object reference to the current database (Experienced_challenge_8.accdb):
Set objDatabase = CurrentDb()
After that simple and straightforward opening we then run smack dab into this line of code:
Set objTable = objDatabase.CreateTableDef("WorkingTable")
What’s going on here? Well, there are a number of different ways we could approach this issue. (In fact, nearly everyone who submitted a solution for challenge 8 attacked the problem from a slightly different angle.) Because we did have a relatively small database to work with, we decided that the easiest solution (and for some of us, the easiest solution is almost always the best solution) was to make a copy of the AnimalFacts table, ask a question, and then, based on the answer to that question, delete the appropriate animals from the copied table. For example, suppose we ask, “Is your animal a mammal?” If the answer is yes, then we’d delete all the animals in the copied table that are not mammals. If the answer is no, then we’d delete all the animals in the copied table that are mammals. After we ask enough questions we’ll eventually have just one animal left in the copied table: the animal you were thinking of.
Note. Or at least that was the plan. As you’ll see, though, we ended up taking a bit of a shortcut in the subroutine we actually wrote.
So how do we make a copy of the table AnimalFacts? Well, as far as we know, there’s no straightforward way to copy a table in Access. Therefore, we need to create the new table (which means specifying all the fields for the table) and then copy the data from AnimalFacts to our new table (which we christened “WorkingTable”). The CreateTableDef method simply creates a workspace of sorts where we can define the new table and all its fields.
Which just happens to be what this chunk of code does:
objTable.Fields.Append objTable.CreateField("ID", dbText)
objTable.Fields.Append objTable.CreateField("Name", dbText)
objTable.Fields.Append objTable.CreateField("Type", dbText)
objTable.Fields.Append objTable.CreateField("Diet", dbText)
objTable.Fields.Append objTable.CreateField("Nocturnal", dbBoolean)
objTable.Fields.Append objTable.CreateField("Lifespan", dbText)
objTable.Fields.Append objTable.CreateField("Weight", dbDouble)
objTable.Fields.Append objTable.CreateField("Group", dbText)
objTable.Fields.Append objTable.CreateField("Protection", dbText)
objTable.Fields.Append objTable.CreateField("Gestation", dbDouble)
All we’re doing here is recreating all the fields found in the AnimalFacts table. To do that, we use the CreateField method to specify a new field for our table. As you can see, CreateField takes two parameters: the field name (e.g., ID) and the datatype for the field (e.g., dbText, a constant that creates a field for storing string values). After defining the new field we then use the Append method to tack this new field onto the Fields collection of the WorkingTable table.
See how that works? And so why do we show 10 lines of code above? You got it: because the AnimalFacts table has 10 fields, and we want WorkingTable to replicate the structure of the AnimalFacts table.
After we’ve defined all the fields we can then Append the new table to the database:
objDatabase.TableDefs.Append objTable
At this point we have a new table – WorkingTable – that replicates the structure of the AnimalFacts table. However, there isn’t any actual data in WorkingTable. Our next line of code is designed to fix that problem:
objDatabase.Execute ("INSERT INTO WorkingTable SELECT AnimalFacts.* FROM AnimalFacts;")
This is just good old-fashioned SQL: the INSERT INTO query enables us to copy all the data from AnimalFacts and insert that data into WorkingTable. And because we’ve been careful to ensure that the field names (and field datatypes) are identical between the two tables that means that, for all intents and purposes, WorkingTable is now an exact duplicate of AnimalFacts.
But, uh, not for long. This might be difficult for you to believe, but we goofed when we created the AnimalFacts table. We named one of our fields Group, which we shouldn’t have done. (Why not? Because Group is a reserved word in the SQL query language, which meant we immediately ran into problems when we tried using the field name Group in our SQL queries.) Fortunately there’s an easy fix to the problem (when you make as many mistakes as we do you get pretty good at fixing problems). This line of code simply renames Group to GroupName:
objDatabase.TableDefs("WorkingTable").Fields("Group").Name = "GroupName"
Now why didn’t we think of that in the first place?
At this point, we’re ready to start playing Twenty Questions. As we noted earlier, the approach we decided upon relies on us asking a question, deleting the appropriate records from WorkingTable, then repeating the process until we either have exceeded the 20 allowed questions or until we’ve guessed the animal we’re thinking of. With that in mind, the first question we ask is this: Is your animal nocturnal? The code for handling this question, and the response, is shown below:
intAnswer = MsgBox("Is your animal nocturnal?", vbYesNo)
If intAnswer = vbYes Then
objDatabase.Execute "DELETE * FROM WorkingTable Where Nocturnal = 0;"
Else
objDatabase.Execute "DELETE * FROM WorkingTable Where Nocturnal <> 0;"
End If
As you can see, in the first line we’re simply using the MsgBox function to ask whether or not the animal is nocturnal. In addition to specifying the text of the question we also tacked on the constant vbYesNo; this adds Yes and No button to the resulting message box. We don’t just display that message box; we also grab the value of the button that was clicked (Yes or No) and stash it in the variable intAnswer.
And what do we do with the variable intAnswer? Well, for starters, we check to see if you clicked Yes; if so, then intAnswer will be equal to the constant vbYes:
If intAnswer = vbYes Then
Let’s say that you did click Yes. That can mean only one thing: the animal you’re thinking of must be nocturnal. If that’s the case, then we use this line of code (and a SQL DELETE query) to delete all the records from WorkingTable where the Nocturnal field is equal to 0. (When we copy the data over, the No values in the Nocturnal field get copied over as 0’s and the Yes values get copied over as -1’s.) That’s what this line of code is for:
objDatabase.Execute "DELETE * FROM WorkingTable Where Nocturnal = 0;"
If intAnswer is not equal to vbYes then we delete all the record where Nocturnal is not equal to 0:
objDatabase.Execute "DELETE * FROM WorkingTable Where Nocturnal <> 0;"
Needless to say, nothing too fancy there.
Incidentally, we didn’t arbitrarily choose to start with the question “Is your animal nocturnal?” Instead, that was a calculated decision on our part. If you thumb through the AnimalFacts table (which would have been a good thing to do before you started writing your code) you would have noticed that there are only a handful of nocturnal animals. That’s why we started with this question: if the animal you were thinking of is nocturnal then we’ve pretty much won the game at this point.
Note. Why have we pretty much won the game at this point? Well, there are only 6 or 7 nocturnal animals in the database. We get 20 questions and we’ve asked 1; that means we still have 19 questions available to us, and only 6 or 7 possible animals that meet the description of the target animal. At this point we can simply start guessing animal names and be assured that we’ll get the answer right before we run out of questions.
After asking whether the animal is nocturnal we then ask whether the animal has a group name; whether the animal is threatened, endangered, or protected; and whether or not the animal is a carnivore. (And, if the animal is not a carnivore, then we ask if it happens to be an omnivore. With those two questions we can then determine whether we are dealing with a carnivore, an omnivore, or an herbivore.) Finally, we ask whether or not the animal is a mammal. Each time we ask a question, and each time we get back a yes or no, we delete the appropriate records from WorkingTable.
That brings us to this block of code:
intQuestionsLeft = 15 - i
Set objTempTable = objDatabase.OpenRecordset("WorkingTable")
intRecordCount = objTempTable.RecordCount
objTempTable.Close
What are we doing here? We’re glad you asked that question. In the first line, we’re simply determining how many questions we have left. We’ve asked at least 5, and possibly 6 if we had to ask if the animal is an omnivore. If we did ask the omnivore question then the counter variable i will be equal to 1; otherwise that counter variable will be equal to 0. We simply subtract the value of i from 15 to determine the number of questions remaining to us.
So we now have 15 (or possibly 14) questions left. Our next chore (which happens to encompass the next four lines of code) determines the number of animals (records) left in WorkingTable. As it turns out, Access has a somewhat annoying habit of reporting a record count different from the count you might expect. When you open a table you get back the number of records in that table. However, if you open a recordset using a query (e.g., “Select * From WorkingTable”) you back the number of records that were accessed. That’s always going to be 1 the first time you open that recordset. In other words, Access will always tell you that you have 1 record in the recordset. That’s really not all that helpful.
Because of that, we have to use a slightly-different route to determine the number of records in the table. We start out by using the OpenRecordset method to directly open the table WorkingTable. After the table is open we can then grab the RecordCount, stash the value in the variable intRecordCount, and then close the table.
So why do we need the record count anyway? Well, what we’re doing here is seeing if we have more questions left than we do animals. Just like with the question “Is your animal nocturnal?” if we have 15 questions left and there are just 11 animals left in WorkingTable, well, we’ve won; all we have to do is loop through the animals still remaining in WorkingTable until we guess the correct animal.
And just how do we do that? Well, for starters we reopen the table WorkingTable and then move the cursor to the first record in the resulting recordset. That’s what these two lines of code are for:
Set objRecordset = objDatabase.OpenRecordset("WorkingTable")
objRecordset.MoveFirst
As soon as we’ve done that we set up a Do Until loop that will continue to run until we reach the end of the recordset:
Do Until objRecordset.EOF
Inside this loop we use this line of code to guess the animal in question; to make sure you know which animal we’re talking about, we use the value of the Name field in our question:
intAnswer = MsgBox("Is your animal the " & objRecordset.Fields("Name"), vbYesNo)
If you answer No then we call the MoveNext record to move to the next record in the recordset and then try again. If you answer Yes, however, we run this block of code instead:
If intAnswer = vbYes Then
MsgBox "I win!"
objRecordset.Close
objDatabase.TableDefs.Delete "WorkingTable"
Exit Sub
End If
Here we simply echo back the fact that the computer won and then close the recordset. After that we use the Delete method to delete WorkingTable from the database, then use Exit Sub to exit the subroutine.
This, by the way, is the block of code we expected to use later in the script, after we had asked a few more questions. However, the first few times we tested the subroutine we found out that we didn’t have to ask any more questions: we always had fewer animals left in the table than we had questions remaining to ask. Does that mean that this subroutine will always win, considering the small number of animals in the database? Well, we can’t say that for sure. But it will win far more often than it will lose.
Which is a heck of a lot better than we usually do!
But what happens if we do have more animals in the table than we have questions remaining? Well, originally our intention was to ask a few more questions (e.g., “Does your animal weigh 100 pounds or more?”). Because we kept winning all the time, however, we decided to forego those additional questions. Instead, if we end up with more animals than questions we do what most people would do when faced with a difficult situation: we give up. Here’s the block of “I surrender” code that runs in that case:
MsgBox "I give up; you win!"
objRecordset.Close
objDatabase.TableDefs.Delete "WorkingTable"
objDatabase.Close
So is that taking the easy way out? Of course it is; would have expected anything other than that?