A Quick Microsoft Access 2000 Tutorial
By Charles W. Neville, © Charles W. Neville, March 2001
It is easier to give examples of queries than to give a formal definition. So consider, for example, the Products table in the Northwind sample database. The Products table lists both current and discontinued products. You can tell which is which by looking at the Discontinued field of the Products table. Suppose you want a list of all current products. It is inconvenient to print out all 77 product records in the table and then run down the list by hand and check off those which are not discontinued. It would be far easier to let the computer do the work by querying the database and getting a machine prepared list of current products. This is where queries come in.
Microsoft Access 2000 allows you to create queries and store them for reuse. The stored queries are listed in the Queries tab of the database window. As it turns out, the designers of the Northwind database have already created and stored the very query we are interested in to list all current products. The name of the query is Current Product List. Let us examine and then run it.
Step 1. Be sure the database window is visible. Click on the Queries tab of the database window to make the list of stored queries visible. Click ONCE on the Current Product List query to select it.
Step 2. Press the Design button at the top of the database window to view the design (query definition) of the Current Product List query.
The design view of the query, which you are looking at now, presents the design of the query in a graphical format. Move the vertical scroll bars in the Product List box up and down to see what fields are available in the Product List table. Notice the checks at the bottom of the ProductID and ProductName columns of the design window. These indicate that the ProductID and ProductName fields will be shown when the query is run. Notice the lack of a check at the bottom of the Discontinued column. This indicates that the Discontinued field will not be shown when the query is run. Finally, notice the No in the Criteria row at the very bottom of the Discontinued column. This indicates that that only those records with No in their Discontinued fields will be selected when the query is run. In this way, the query will list the product ID's and names of all current products, but will not list any discontinued products.
Note. You may have wondered, where did the Product List table come from? After all, the correct name of the table in the database is Products. The answer is that Product List is an alias for the Products table. This introduces an unnecessary complication into the query, and I suppose the designers of the Northwind database used this alias just to prove they could.
Another note. The graphical format used by design view has another name, Query By Example or QBE, so design view could equally well be called QBE View. Of course, Microsoft doesn't call it that, perhaps because QBE was invented by IBM. QBE was originally a simple text based method for entering queries. The second generation of QBE became known as graphical QBE because it used a tabular graphical interface similar to the one used by Microsoft and many other database vendors today. The graphical interface for second generation QBE was much simpler than Microsoft Access 2000's because second generation QBE, despite its name of graphical QBE, ran on the text based terminals used by the mainframes of the time. Jeffrey D. Ullman's book, Principles of Database Systems, devotes an entire chapter to the original version of QBE, and Roger Jennings' Using Access 2000 contains an excellent brief history of graphical QBE.
Step 1. Be sure the Current Product List query is open in design view. Click on the Query menu and then click on Run to run the query. (Alternate procedure: Click the ! button on the tool bar.)
If all goes well, the Current Product List query will run rather quickly, and the following table of results will appear,
This table of results is referred to as the Datasheet View of the query.
Move the vertical scroll bars at the right of the table up and down to view all 69 records in the table of results. This is a lot easier than examining all 77 records in the original table by hand, isn't it?. And with real data, where there may be thousands or even millions of records, machine run queries are essential.
A query is really an SQL (Structured Query Language) statement or statements. Microsoft Access 2000 makes it possible for you to examine and edit the actual SQL statements making up a query by switching to SQL View. Let us switch to SQL View and look at the SQL statements making up the CurrentProductList query.
Step 1. Be sure the Current Product List query is either open in datasheet view, or selected in the Queries tab. Click on the View menu and then click on SQL View to look at the query in SQL view.
If all goes well, you will see the following window appear,
This window is actually a simple text editor. You can edit the SQL, enter more SQL, and cut, copy and paste text. Thus, if you already have some SQL queries prepared in a text file, say one you wrote in Notepad or UltraEdit 32, you can create an Access 2000 query by clicking on Select Query from the Query menu to start a new query, switching to SQL view, deleting the small amount of text in the SQL view window, and then pasting in the text of your prepared SQL query. The really nice thing is that if you switch back to design view, your query will appear there nicely in graphical QBE. Of course, you can run your new query as we just did. You will get to do these things when you study SQL Queries.
Note. The SQL produced by Access 2000 when you create a QBE query in design view is often unnecessarily complicated. In the example above, there are lots of unnecessary parentheses. This is typical of machine generated code, and Access 2000 should not be criticized on this account.
The last thing you should do is close the Current Product List query by clicking the small x at the top right of the SQL View window on its title bar. Be careful not to click on the x at the top right of the Microsoft Access title bar, or you will close Access completely.
The next thing to do is to study how to create your own QBE Queries. After that, you will be prepared to study how to create SQL Queries.