CSCI A114 / INFO I111 Lab Notes Four First semester 2000-2001

1. Open the Bavant Marine Services database and make sure that the Marina table is selected.

Open a new Select Query window.

2. Right-click any open area in the upper pane of the Query1: Select Query window.

The shortcut menu displays.

3. Click Show Table on the shortcut menu.

The Show Table dialog box displays.

4. Click Technician to select the Technician table and then click the Add button.

Close the Show Table dialog box by clicking the Close button.

5. Expand the size of the field list so all the fields in the Technician table display.

Include the Marina Number, Name, and Tech Number fields from the Marina table and the Last Name and First Name fields from the Technician table.

6. The fields from both tables are now included. Run the query.

The results display and contain data from both the Marina and the Technician tables.

7. What query did you just design and create?

Write your answer below:

8. Click the View button to return to Design view. Add the Warranty field to the query.

The Warranty field displays in the design grid. Type >1000 as the criterion for the Warranty field and then click the Show check box for the Warranty field to remove the check mark.

9. A criterion is entered for the Warranty field and the Show check box is empty, indicating that the field will not display in the results of the query.

You're ready to run the query.

10. Run the query.

The results display. Only those marinas with a warranty amount greater than $1,000 display in the result. The Warranty field does not display.

11. Can you write the QCM for the above query?

Write your answer here:

12. Click the View button to return to Design view. Right click any field in the Technician table field list.

The shortcut menu displays. Click Remove Table to remove the Technician table from the Query1: Select Query window.

13. On the Edit menu, click Clear Grid.

Include the Marina Number and Name fields.

14. Right-click the Field row in the third column in the design grid and then click Zoom on the shortcut menu. The Zoom dialog box displays.

Type Total Amount:[Warranty]+[Non-warranty] in the Zoom dialog box that displays.

15. The expression you typed displays in the dialog box. Click the OK button.

The Zoom dialog no longer displays. A portion of the expression you entered displays in the third field in the design grid.

16. Run the query and check the results.

The results display. Microsoft Access has calculated and displayed the results.

17. What is the SQL for this new query?

Can you write the QCM for it?

18. Click the View button to return to Design view.

On the Edit menu, click Clear Grid.

19. Right-click the grid.

The shortcut menu displays.

20. Click Totals on the shortcut menu and then include the Warranty field. Point to the Total row in the Warranty column.

The Total row now is included in the design grid. The Warranty field is included, and the entry in the Total row is Group By. The mouse pointer, which has changed shape to an I-beam, is positioned on the Total row under the Warranty field.

21. Click the Total row in the Warranty column, and then click the arrow that displays.

The list of available selections displays.

22. Click Avg.

Avg is selected.

23. Run the query.

The result displays, showing the average warranty amount for all marinas.

24. Click the View button to return to Design view.

Include the Tech Number field in the design grid. Produce the list of available options for the Total row entry just as you did when you selected Avg for the Warranty field.

25. The list of available selections displays.

The Group By entry in the Tech Number field may not be highlighted on your screen depending on where you clicked in the Total row.

26. Use the vertical scroll bar to move through the options until the word, Where, displays.

Click Where. Type 36 as the criterion for the Tech Number field.

27. Where is selected as the entry in the Total row for the Tech Number field and 36 is entered as the Criterion.

Run the query.

28. The result displays giving the average warranty amount for marinas of technician 36.

Click the View button to return to Design view. On the Edit menu, click Clear Grid.

29. Include the Tech Number field. Include the Warranty field, and then select Avg as the calculation in the Total row.

The Tech Number and Warranty fields are included. Group By currently is the entry in the Total row for the Tech Number field, which is correct; thus, it was not changed.

30. Run the query.

The result displays showing each technician's number along with the average warranty amount for the marinas of that technician.

31. Click the view button and then click the Save button.

Type Average Warranty Amount by Technician and then point to the OK button.

32. The Save As dialog box displays with the query name you typed.

Click the OK button to save the query, and then close the query by clicking the Query window's Close Window button.

33. Access saves the query and closes the Query1: Select Query window.

Once you have saved the query, you can use it at any time in the future by opening it.

34. To open a saved query, click the Queries object in the Database window, right-click the query, and then click Open on the shortcut menu.

The query is run against the current database.

35. Thus, if changes have been made to the data since the last time you ran it, the results of the query may be different.

A Case Perspective Summary and Project Summary follow.

You have been successful in assisting the management of Bavant Marine Services by creating and running queries to obtain answers to important questions. You used various types of of criteria in these queries. You joined tables in some of the queries. Some Bavant Marine Services queries used calculated fields and statistics. Finally, you saved one of the queries for future use.

Project Summary

In Project 2 (Labs 3-4) you created and ran a variety of queries. You learned how to select fields in a query. You used text data and wildcards in criteria. You also used comparison operators in criteria involving numeric data. You combined criteria with both AND and OR. You learned how to sort the results of a query, how to join tables, and how to restrict records in a join. You created computed fields and calculated statistics. You learned how to use grouping as well as how to save a query for future use.

You can now start working on, and finish assignment #2 and THQ#2.