Home

Queries Fundamentals

The Query Wizard

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 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:

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
     
    Simple Query Wizard
  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

S6  Print database objects (tables and queries)
S7 Navigate through records in a table, query, ...
S8 Create a database (by using a wizard ...)
S9 Create tables by using the Table Wizard
S23 Enter records using a datasheet

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  
Previous Copyright © 2002-2019, FunctionX Next