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

1. Open the Bavant Marine Services database.

The database opens, and the Bavant Marine Services: Database window displays.

2. If the Tables object is not already selected, click Tables on the Objects bar.

Make sure that the Marina table is selected.

3. You are now ready to create your first query.

Click the New Object: AutoForm button arrow, on the Database window toolbar.

4. Point to Query on the New Object: AutoForm menu, and click on it.

The New Query dialog box displays. Make sure Design View is selected, then click the OK button.

5. The Query1: Select Query window displays and the Query Design toolbar replaces the Database window toolbar.

Maximize the Query1: Select Query window, then point to the dividing line that separates the upper and lower panes of the window: the mouse pointer will change shape to a two-headed arrow with a horizontal bar.

6. The upper pane contains a field list for the Marina table. The lower pane contains the design grid, which is the area where you specify fields to be included, sort order, and the criteria the records you are looking for must satisfy.

Drag the line down; the two panes will be resized. Then move the mouse pointer to the lower edge of the field box (in the upper pane) so it changes shape to a two-headed arrow.

7. Drag the lower edge of the field box down far enough so all fields in the Marina table are visible.

Now the Query1: Select Query window is maximized, contains a field list for the Marina table in the upper pane of the window, and an empty design grid in the lower pane.

8. Double-click the Marina Number field to include it in the query: the Marina Number field is included as the first field in the design grid.

Double-click the Name field to include it in the query. Include the Tech Number field using the same technique. The Marina Number, Name, and Tech Number fields are now included in the query.

9. Now point to the Run button on the Query Design toolbar and click on it. The query is executed and the results display. The Query Datasheet toolbar replaces the Query Design toolbar. The Sort Ascending button on the Query Datasheet toolbar now occupies the position of the Run button.

The data displays. Notice that an extra blank row, marking the end of the table, displays at the end of the results. If you want you can print the results of the query: simply click the Print button on the toolbar (when the printer is ready).

10. What query did you just design and run?

Write your answer here:

11. Now point to the View button arrow on the Query Datasheet toolbar, then click on it. The View button menu displays.

Click Design View. The Query1: Select Query window displays.

12. Click the Close Window button for the Query1: Select Query window. The Microsoft Access dialog box will display. Clicking the Yes button saves the query, and clicking the No button closes the query without saving.

Click the No button. The Query1: Select Query window closes and is removed from the desktop.

13. Open a maximized new Query1: Select Query window containg a field list for the Marina table in the upper pane and an empty design grid in the lower pane (see steps 3-7 above to create the query and resize the window).

A maximized Query1: Select Query displays. The two panes have been resized.

14. Double-click the asterisk in the field list: the table name, Marina, followed by a period and an asterisk is added to the design grid, indicating that all fields are included. Then point to the Run button on the Query Design toolbar, and click on it.

The results display, and all the fields in the Marina table are included. The Tech Number may not, as it may not fit on the screen. Click the View button on the Query Datasheet toolbar to return to Design View.

15. What query did you just design and run?

Write your answer here:

16. Click Edit on the Menu bar. The Edit menu displays. Click Clean Grid. Access clears the design grid so you can enter your next query.

Now, one by one, double-click the Marina Number, Name, Warranty, and Non-warranty fields to add them to the query.

17. The Marina Number, Name, Warranty, and Non-warranty fields are added to the design grid. Point to the Criteria row for the first field in the design grid: the mouse pointer on the Criteria entry for the first field (Marina Number) has changed shape to an I-beam.

Click the Criteria row, type EL25 as the criterion for the Marina Number field. The criterion is entered. Then click the Run button: the results display.

18. Only marina EL25 is included. The extra blank row contains $0.00 in the Warranty and Non-warranty fields. Unlike text fields, which are left blank, number and currency fields in the extra row contain 0, and the format is specific.

What query did you just design and run? Write your answer below:

19. Click the View button to return to Design view. Click the Criteria row under the Marina Number field and then use the Delete or Backspace key to delete the current entry (EL25).

Then click the Criteria row under the Name field. Type LIKE Fe* as the entry. (The criterion is entered). Then click the Run button.

20. The results display. Only the marinas whose names start with Fe are included.

What query did you just design and run?

21. Click the View button to return to Design view. On the Edit menu, click Clear Grid. Access clears the design grid so you can enter the next query.

Include the Marina Number, Name, Address, Warranty, and City fields in the query. Type Burton as the criterion for the City field and then point to the City field's Show checkbox.

22. Click the Show checkbox to remove the check mark. The City field will not show in the result. Access has added quotation marks before and after Burton automatically. Now run the query by clicking the Run button.

The results display. The City field does not display. The only marinas included are those located in Burton. What query did you just design and run?

23. Click on the View button to return to Design view. On the Edit menu, click Clear Grid. Access clears the design grid so you can enter the next query.

Include the Marina Number, Name, Warranty and Non-warranty fields in the query. Type 0 (zero) as the criterion for the Non-warranty field. You need not enter a dollar sign or decimal point in the criterion).

24. Run the query by clicking the Run button. The results display. Only those marinas that have a non-warranty amount of $0.00 are included.

What query did you just design and run?

25. Click the View button to return to Design view. On the Edit menu click Clear Grid. Access clears the design grid so you can enter the next query.

Include the Marina Number, Name, Warranty, and Non-warranty fields in the query. Type >1000 as the criterion for the warranty field. The fields are selected and the criterion is entered.

26. Run the query.

The results display; only those marinas that have a warranty amount greater than $1,000 are included. What query did you just design and run? Write your answer below:

27. Click the View button to return to Design view. Include the Tech Number field in the query. If necessary, click the Criteria entry for the Warranty field, and then type >1000 as the criterion for the Warranty field. Click the Criteria entry for the Tech Number field and then type 36 as the criterion for the Tech Number field.

Criteria have now been entered for the Warranty and Tech Number fields. Because both conditions are on the same line, both must be true.

28. Now run the query. Only the single marina whose warranty amount is greater $1,000.00 and whose technician number is 36 will be included.

What query did you just design and run?

29. Click the View button to return to the Design view. Click the Criteria entry for the Tech Number field. Use the Backspace key to delete the entry ("36"). Click the or row (below the Criteria row) for the Tech Number field, then type 36 as the entry. The criteria are entered for the Warranty and Tech Number fields on different lines. Because conditions are on different lines, only one needs to be true.

Now run the query. The results display. Only those marinas whose warranty amount is greater than $1,000.00 or whose technician number is 36 are included. What query did you design and run? Write your answer below:

30. Click the View button to return to Design view. On the Edit menu, click Clear Grid. Include the City field in the design grid. Click the Sort row below the City field, and then click the Sort row arrow that displays.

A list of available sort orders displays. Click Ascending; Ascending is selected as the sort order. Now run the query. The results contain the cities from the Marina table. The cities display in alphabetical order. Duplicates, that is, identical rows, are included. What query did you just design and run? Please write your answer below:

31. Click the View button to return to Design View. On the Edit menu, click Clear Grid. Include Marina Number, Name, Tech Number, and Warranty fields in the query in this order. Select Ascending as the sort order for the Tech Number field and Descending as the sort order for the Warranty field. Then run the query.

The results display. The marinas are sorted by technician number. Within the collection of marinas having the same technician, the marinas are sorted by descending warranty amount. (Overall order is by technician number). What query did you just design and run? Write your answer here:

32. Click the View button to return to Design view. On the Edit menu, click Clear Grid. Include the City field, click Ascending as the sort order and right-click the second field in the design grid (the empty field following City).

You must right-click the second field or you will not get the correct results. The shortcut menu displays. Click Properties on the shortcut menu.

33. The Query Properties sheet displays. Click the Unique Values property box, and then click the arrow that displays to produce a list of available choices for Unique Values. Click Yes and then close the Query Properties sheet by clicking its Close button.

Run the query. The results display and the cities are sorted alphabetically. Each city is included only once; what query did you just design and run? Write your answer below:

You are now ready to start working on assignment #2 and THQ2.