MICROSOFT ACCESS 97 LAB SESSION 1 - SIUE



Dr. Bordoloi

MICROSOFT ACCESS LAB 1 ANSWERS

Task8: Use One-Table Queries

Query A: List all the author names with a last name beginning with one of the letters A, C or H.

1. With Query 1 highlighted, click on Design.

2. Leave what you already have in the first column alone. Still in the first column, go to the line below Like “C*” and type in A*. Go to the line below A* and type H*. Placing different conditions on different lines of a query is the way to get an “or” condition to work in Access.

3. Click on the Exclamation Point.

Query B: List the Author Numbers of any second authors from the WROTE table.

1. Close answer table.

2. At the database window, click on Queries, then New.

3. Click on Design view, then OK.

4. Select WROTE table, then Add, then Close.

5. This time drag the Author Number name from the top part of the window to the first column below.

6. Then drag the Sequence Number name from the top part of the window to the second column below.

7. In the criteria row of the Sequence Number (second) column, type the number 2.

8. Click on the Exclamation Point.

Task 9: Use Two-Table Queries

Query C: List the Author Names of any second authors.

1. Close answer table and get back to the database window.

2. Click on Queries, then New.

3. Click on Design View, then OK.

4. Select AUTHOR table, then Add. Select WROTE table, then Add, then Close.

5. Drag the Author Name name from the AUTHOR table box in the top part of the window to the first column below.

6. Drag the Sequence Number name from the WROTE table box in the top part of the window to the second column below.

7. In the criteria row of the Sequence Number (second) column, type the number 2.

8. Click on the Exclamation Point.

Query D: How many books in this list has Dick Francis written? (It makes no difference whether as author or co-author.)

No detailed instructions here, but you will want to use the same two tables as above. Drag the Author Name name to the first column, and type Francis* in the criteria row. This entry says to match on Francis plus any other characters and spaces that may follow. An alternative way of entering this criterion is to type “Francis, Dick”. Drag the Book Code name to the second column. Now something new; Click on the big sigma icon on the toolbar. Click on Total row of the Book Code column, click on the drop-down arrow and choose count. This will give us a count of all the Book Codes corresponding to Francis, Dick. To add a more meaningful heading to the answer table, type Count of Books, then a colon, before Book Code in the first row of the second column.

Query E: What authors have authored or co-authored two or more books?

One solution:

Select AUTHOR and WROTE tables

Drag Author Name from AUTHOR table to first column

Drag Book Code from WROTE table to second column

Click on Totals icon (sigma)

In the Total row of Book Code column, click on drop-down arrow

and select Count

In the Criteria row of Book Code column, enter >=2

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download