Beginning Access students tend to have little trouble creating a database or form. The step-by-step instructions given in the first lessons seem almost too easy for them.
Why then, do they find creating queries and reports so troublesome? Could it be that their success with the first lessons was not due to their understanding of databases but to their ability to enter data into a table and use Windows wizards—skills they already mastered in Word and Excel?
If you design your Access class to focus on practical, everyday applications, instead of starting with database terminology such as queries and variables, your students will catch on more quickly and work more successfully.
Teach decision-making, not databases
Most new Access users are not database administrators or programmers. Instead, they are preparing for careers in teaching, nursing, business, law, or social work. Tables, records, fields, and data types simply have little bearing on these students’ interests. What is of interest to them is how they can get the information they need to make professional decisions.
I knew that in order for them to get that information, they must, at the very least, be familiar with some basic query and reporting tools, such as those used in Microsoft Access. So, why not teach Access as a decision-making tool?
The first lesson in query tools
When teaching Access as a decision-support tool, the focus shifts from database creation to asking the right questions. Thus, a first lesson would involve three steps:
- Examine a simple database in terms of the data it contains.
- Define questions that can be answered from the data.
- Translate each question into an Access query.
Keep it simple
When demonstrating the Access query tools, work with tables that contain a relatively small number of records—five to 10. With a limited number of records, students can look through the data themselves to see if their query results are correct.
For example, one database I used in a class consisted of just two tables: a customer table containing 10 retail store records and an employee table containing records of four account managers assigned to those stores.
Teaching field names
Most query and reporting tools involve dragging and dropping field names onto a grid. Access not only helps students become familiar with such tools, it also teaches them how to use field names to ask questions of the data.
First, have the students familiarize themselves with the data in the table and how that data relates to the fieldnames. Before they can construct a reliable query, the students must understand what the fields in the database represent. Point out that database fieldnames won’t necessarily conform to the words they will use in asking their question.
For instance, the customer table had the following fields: Customer ID, Name, Address, City, State, Zip, Balance, and Account Executive ID. The Name field in this case refers to the name of a store with which the company does business, not a person.
Asking the right questions
The key to using any query tool is asking the right question. Using the customer table, I proposed questions similar to the following. (Your students may come up with some of their own.)
- Which stores have a balance greater than $100?
- Which stores are located within the 59075 ZIP code area?
- Which stores are located within any ZIP code area that begins with 59?
- Which stores are located in Missouri and have a $0 balance?
For each question, show the students how they would enter criteria in the Query Design grid to obtain an answer. For example, the first question would require that you enter >100 in the Balance field criteria cell. The second question would have you enter 59075 in the Zip field criteria cell, while the third question would have you enter 59 followed by a wildcard character. The fourth question requires you to enter two criteria, both of which must be true in order for a store to be listed in the query results.
Moving on to multi-table queries
Table joins is often a difficult concept for students to master. Here again, introducing the concept by proposing questions (this time involving two tables) can make it a lot easier.
Have the students examine the data in the second table and show its relationship to the first table by its key field. For example, the employee table I used had the following fields: First Name, Last Name, Account Executive ID, Address, City, State, and Zip. I explained how the Account Executive ID field related the two tables to each other. Then, I proposed a question that can be answered by querying the data in both tables:
“Which stores have been assigned to Mary Jones and have a balance over $100?”
Next, I showed them how to use the Show Table button in the toolbar to add the second table to the Query Design grid, and then how to enter criteria so data from both tables will be used to answer the question.
What if the query results don’t make sense?
To reinforce these concepts, I gave the students a similar set of questions to use against the demo database. They were to find the answer to each question and list the criteria and the corresponding fields used to construct the query.
After performing a manual check of the table data, students may find that their query results are incorrect. There could be a number of reasons for this. First, check their criteria. A typical error is a data type mismatch. For example, many students enter >$100 as the criteria for a numeric field such as Balance.
Remember to clear the grid!
One of the biggest areas where students get into trouble is that they forget to delete the criteria from a previous query before they create a second one. Show them how to clear the grid before creating a new query. Clicking on Edit in the Menu bar and then on Clear Grid does this.
Check the data definitions in Table Design view
Sometimes the student may create a query that leads to a type mismatch error, even though the criteria entered appears to be correct. For example, one student’s job was to keep track of overdue invoices. Using an Invoice database from the course material, he developed a query that determined which ones were overdue by entering the appropriate criteria in the Invoice Date field criteria cell.
Although the query was correctly constructed, he kept getting errors. Upon examination of the Invoice Date field in the Table Design view, we found its data type to be Text instead of Date, making it impossible to run his query.
On to reporting tools and beyond
After mastering these basic query techniques, students should be ready to create a report. Have them save one of their queries and use the Access Report Wizard to create a printed report from that query.
From here, students will want to begin using their Query Design skills to ask more sophisticated questions of the data, such as “What is the average balance for all stores assigned to Mary Jones?” or “Which stores are located in Illinois or are assigned to Bob Smith?”
For many students, just mastering a few basic query and reporting techniques gives them enough confidence to immediately begin using Access for making decisions on the job.
What techniques do you use to get students started using Access? Do you have sample queries and databases that you use in every class? Share your Access teaching tips with us so we can use them in another article.