A Quick Microsoft Access 2000 Tutorial
By Charles W. Neville, © Charles W. Neville, March 2001
You need to become familiar with SQL (Structured Query Language) because you will need it if you ever develop a Microsoft Access application and have to issue queries through code. You also need SQL to query other database systems, such as Oracle and MySql. Finally, once you know SQL, it is often easier to write a query directly in SQL than to design it using Access's QBE interface.
As Roger Jennings observes in his excellent book, Using Access 2000, one of the best ways to learn SQL is to build QBE queries and then inspect and alter the query in SQL View. You are going to do just that with the CurrentProductsAndSuppliers query that you developed and saved in the last chapter. In detail, you are going to copy the SQL from the CurrentProductsAndSuppliers query, start a new blank query, paste the SQL into the new query, and save the new query under a new name. Then, you are going to change the SQL behind the new query.
Step 1. Be sure the CurrentProductsAndSuppliers query is visible in SQL view. By now, you know how to open the query and change to SQL view if it is not.
Step 2. Select the SQL text and copy it to the clipboard using the Edit menu.
Step 3. Close the query by clicking on the small x at the top right of the SQL View window on its title bar. (Be careful not to close Microsoft Access by clicking on the x at the top right of the Microsoft Access title bar.) If a dialog box comes up asking you if you want to save the changes to the query, answer No.
Step 1. Be sure the Queries tab is visible. Press the New button to start a new query. When the New Query dialog box appears, be sure that Design View is selected and press OK.
Step 2. Because you want to start a new blank query, press Close as soon as the Show Table dialog box appears.
You should see a new blank query in design view, with no tables.
Step 3. Change to SQL view. By now, you know how.
Step 1. Paste the SQL you have previously copied to the clipboard into the new query's SQL view window by selecting the text in the window if necessary, clicking on the Edit menu, and then clicking Paste.
The result will be that you have the SQL from the CurrentProductsAndSuppliers query pasted into the SQL view window of your new query
The SQL view window is a complete, though simple, text editor. You are going to simplify the SQL you have just pasted in by editing it.
Step 1. The parentheses in the SQL are not needed, so delete them.
Step 2. The INNER JOIN statement is not needed either, so remove it too. (Remove everything after the FROM on the INNER JOIN line.) You do need to say what tables you are using though, so insert PRODUCTS, SUPPLIERS after the FROM. The simplified SQL in the SQL view window of the new query should look like
Remember, you should always test a new query by running it.
Step 1. Run the completed query.
Note that the table of results is the same as that for the CurrentProductsAndSuppliers query. Thus the new query appears to be equivalent to the CurrentProductsAndSuppliers query, even though the SQL has been greatly simplified by replacing the INNER JOIN by a WHERE clause. Most people who write SQL use WHERE clauses instead of JOINs because WHERE clauses are so much simpler.
Save the simplified new query for future use as SQLQuery. (The period is there for grammatical reasons and is not part of the name.) Yes, the name SQLQuery is a dumb one, but there are only so many variations on the more descriptive CurrentProductsAndSuppliers name.
Once a query has been built with SQL, it has a perfectly reasonable design view (QBE view). Let us verify this by displaying the SQL query in design view.
Step 1. Change to the Design View of the query. By now, you know how.
Note that the design view of the SQL query is nearly the same as the design view of the CurrentProductsAndSuppliers query. Note the two tables displayed in the tables panel of the design view window. Only the line denoting the Access many-one relationship is missing. Note how similar the tabular QBE panel of the SQL query is to the QBE panel of the CurrentProductsAndSuppliers query. Only the order of some of the columns is different.
You can switch back and forth between designing a query in SQL View and Design View at will. You should mix and match, and use whatever is simplest at the time.
Congratulations! You have finished A Quick Microsoft Access 2000 Tutorial. If you want to review, you should go back to the introduction to select the chapters you want to revisit.
If, for any reason, you want to delete any of the queries you have saved, you select the query to be deleted and use Delete from the Edit menu.
There is much more to learn about Microsoft Access, especially about Forms. In Microsoft applications, Forms are windows used to interact with the application. Most Microsoft Access databases use Forms to make it easy for people to interact with the database, so it behooves computer science students to become familiar with Forms.
If want to satisfy your curiosity about forms, try selecting the Forms tab and then open the Main Switchboard form. Then use it to open various other forms in the Northwind sample database. By now, you know enough to switch back and forth between form view and design view, so you can learn something about how forms are constructed. Each form has an associated code module, written in a form of Visual Basic called VBA (Visual Basic for Applications). To view the code, switch to code view.
If you really want to find out how to build your own forms and develop Microsoft Access applications in VBA, try reading Roger Jenning's excellent book, Using Access 2000, published by Que Press.
To close Microsoft Access 2000, click on the File menu and then click on Exit. (Alternate procedure: Click on the small x at the very top right of the Microsoft Access window.) But I'm sure you already know that.