Queries Fundamentals

A query is a request you present to the database, and the database displays its response to you. The whole purpose of creating a query lies on how you formulate that request. There are three main ways you create a query in Microsoft Access:

• The Query Wizard offers the simplest approach where in a step by step process you define the purpose of the query and the database will display its answer
• The best way to build a query is by using the Design View
• Once you get familiar with queries, you will find out that a query is just a group of words called a statement you formulate. This means you can write that statement manually. This approach is the most complex because you need to know the structure of a query statement, but it provides more control than the other two techniques.

The simplest way to create a query is by using the Query Wizard. The wizard presents the tables that are part of the database and you select which fields you need. Such a query is called a Select Query.

Data used on a query can originate from a table, another query, or a combination of tables and/or queries. The first page of the Simple Query Wizard expects you to choose the origin of the query, starting with the table or the query name. For example, you can use a table of employees, then, you choose the necessary data that you want to filter for your query. You can use this query to retrieve the employees' data by their employee number, their last name, the date they were hired, their salary, their marital status, their E-Mail address, or the city or state they live in.

To use the Query Wizard, on the Database Window, you can click the Queries button and then double-click Create Query By Using Wizard. Alternatively, you can first display the New Query dialog from where you would choose Simple Query Wizard. To display the New Query dialog box:

• On the toolbar of the Database window, while in the Queries section, you can click the New button
• On the main menu, you can click Insert -> Query
• On the Database Toolbar, you can click the arrow of the New Object button and click Query

When building a query in the first page of the Simple Query Wizard , you must first specify the object (a table or another query) that will provide data. Then select items using the same types of buttons we reviewed for the Table Wizard.

 

Practical Learning: Creating a Query Using The Wizard

1. The Rockville Techno database should still be opened
   On the Database Window, click the Queries button
2. To create a query, from the Database window, double-click the Create Query By Using Wizard button
   (If you are using MS Access 97, click New, click Simple Query Wizard, and click OK)
3. From the Tables/Queries combo box, select Table: CompanyAssets
4. From the Available Fields list box, double-click AssetType, Make, Model, DateAcquired, and PurchasePrice
    
   
5. Click Next twice
6. Give the title AssetsInventrory and make sure the Open The Query To View Information radio button is selected
    
   
7. Click Finish

Data Entry on a Query

A query uses the same approach to present its data, like a table: it is made of columns and rows whose intersections are cells. Although the main purpose of a query is to prepare data either for analysis or isolate some fields to make them available to other database objects, as done on a table, data can be entered in a query.

Data entry on a query is the same as done on a table: data is entered into cells. The Enter, Tab and arrow keys are used with the same functionality. Like the table, the query provides navigation buttons on its lower section, allowing you to move to the first, the previous, the next, the last or any record in the range of those available. 

Practical Learning: Performing Data Entry on a Query

1. While the AssetsInventory query is still opened, click the first empty field under the AssetType column
2. Type Computer and press Enter
3. Complete the query as follows:
    

   AssetType	Make	Model	Date Acquired	Purchase Price	Notes
   Printer	HP	LaserJet 4200dtn	10/08/2002	1950.95	B/W Printer
   Computer	IBM	NetVista M42	10/08/2002	1035.00
   Laptop	Gateway	200XL	12/05/2002	2095.95	Business Notebook
   Printer	Xerox	Phaser 8200	10/22/2002	1250.55	Color Printer
   Digital Camera	Olympus	C-50	11/06/2002	450.75
   Computer	IBM	ThinkCentre S50	10/08/2002	1055.55
   Computer	Ctrl + '	Ctrl + '	10/20/2002	1120.55
   Ctrl + '	Ctrl + '	Ctrl + '	Ctrl + '	Ctrl + '

4. After using the query, close it
5. To create a query of selected information of employees, on the main menu, click Insert -> Query
    
   
6. In the New Query dialog box, click Simple Query Wizard and click OK
7. In the first page of the wizard, in the Tables/Queries combo box, select Tables: Employees
8. In the list of Available Fields, double-click FirstName, LastName, Title, and BillingRate
9. Click Next twice
10. Change the Title of the Query to Employees Pay Information and click Finish
11. After viewing the result of the query, close it

Query Printing

Like tables, queries provide you with a fast means of printing data. Once again, this should be done when you need a printed but not a professional-oriented product. Data printing on a query is done with the exact same approaches and techniques as for a table.

Practical Learning: Printing a Query

1. On the Database Window, while still in the Queries section, double-click the AssetsInventory query
2. To print the query, on the main menu, click File -> Print…
3. Make sure a printer is selected in the Name combo box and click the Properties button
4. Select the Portrait orientation and click OK
5. Click OK on the Print dialog box
6. To print only a few records, click the gray box on the left side of Laptop
7. Press and hold Shift. Then click the gray box on the left side of Digital Camera and click release Shift
    
   
8. On the main menu, click File -> Print… and, on the Print dialog box, click Selected Record(s)
9. Click OK to print
10. After using the query, close it. 

MOUS Topics

Exercises 

Tenley Associates

1. Start a new database using the Database Wizard and based on the Expenses sample database
2. Name it Tenley Associates
3. For the Information About Employees, add Department Name, Email Name, Home Phone, and Date Hired
4. For the Expense Report Information, include the Business Purpose
5. Set the Screen Style to Stone
6. Set the Printed Reports Style to Corporate
7. Set the Database Title to Tenley Associates
8. Set the Startup form to None
9. From the Tables section of the Database window, open the Expense Categories table and enter the following expenses:
    

   Expenses Category ID	Expenses Category	Expense Account#
   1	Office Equipment	102
   2	Car Rental	104
   3	Transportation	105
   4	Customer Entertainment	108
   5	Other	112

10. Print all records on the table and close it
11. Open the Employees table and fill it up with the following values, ignoring those not mentioned:
     

    First Name	Last Name	Title	Home Phone
    James	Bidds	Accountant	(301) 725-4570
    Alexandra	Roberts	Sales Manager	(410) 727-0982
    Walter	Hoaks	Sales Representative	(410) 521-2850
    Bertine	Baugh	Manager	(301) 912-2129

12. Close the table
13. Using the Simple Query Wizard, create a query based on the Employees table and include the following fields: FirstName, LastName, Title, and HomePhone. Save the query as Employees Contact Information
14. Close the query and print all of its records without opening it
15. Using the Employees Contact Information query, print only the second and third records of employees
16. Use the Table Wizard to create a table that includes the following fields: DepartmentID (from the Assets sample table), DepartmentName (from the Employees sample table), and Notes (from the Recipes sample table of the Personal category). Save the table as Departments and fill it up with the following values
     

    Department ID	Department Name	Notes
    1	Administration	Including all personnel from Management
    2	Sales	Including all sales representatives and managers
    3	Accounting