IS223 Microsoft Access 2016 Tutorial 2 PDF

Preview

Summary

Tutorial for homework assignments...

Description

Microsoft Access Lab Session II 2013 Version Queries from Homework Exercise (Lab Session I) Note: These queries are based on the five tables described in Microsoft Access Lab Session I. The tables were to be named AUTHOR, WROTE, INVENTORY, BOOK, and PUBLSHER or something similar. Doing the below 3 queries will assist you with doing the homework also. These are practice exercises and do not need to be turned in as part of the homework assignment Additional Practice Queries Query G: List any mystery books, and their authors, that were NOT written by Dick Francis. 1. 2. 3. 4. 5. 6. 7.

Click on Create, then Query Design while in Design View. Select Author, Book, and Wrote tables Drag “Book Title” from Book table to first column Drag “Book Type” from Book table to second column Drag “Author Name” from Author table to third column Select MYS from the Criteria row of “Book Type” column Enter not “Francis, Dick” in Criteria row of “Author Name” column

Query H: For each book on hand in each branch, list the Book Title, Book Type, Branch Number, and the Total Retail Value of the books on hand at that branch (On Hand times Book Price). 1. 2. 3. 4. 5. 6.

Click on Create, then Query Design while in Design View. Select Book and Inventory tables Drag “Book Title” to first column Drag “Book Type” to second column Drag “Branch Number” to third column In fourth column, enter Total Retail Value: [On Hand] * [Book Price]

Query J: For each computer science book on hand, list the Book Title, Book Type, Book Price, and the total number of copies on hand in all of the branches. You need to do Query J because the table you create is the one you will use to make the report in the tutorial assignment.

1. Select Book and Inventory tables 2. Drag “Book Title” to first column 1|Page of 5

3. 4. 5. 6. 7. 8. 9.

Drag “Book Type” to second column Drag “Book Price” to third column Drag “On Hand” to fourth column Enter CS in Criteria row of “Book Type” column Click on Totals toolbar button Change entry in Total row of “On Hand” column to Sum You can change the Heading that appears in the column on your query by placing the words you desire in front of the column heading and placing a “:” after it. My new title for column: On Hand i. This time, rename the query as a table. ii. Close the query and save it. iii. Open Query J in Design View (select Query J, click on Design) iv. Change query type to Make Table by clicking on drop-down arrow to left of exclamation point, then select Make Table v. Give the new table the name Book Type vi. Click on Run

Preparing a Report Using Report Wizard 1. Take a look at Book Type table first to make sure it is what you want. 2. Click on the Create tab, and then click Report Wizard.

3. From the pop-up box, select the Book Type table. 4. Select all fields for the report, either by highlighting each one in turn and clicking on >, or by clicking on >>; click on Next. 5. Click “Book Type”, press >, and click Next. 6. Choose “Book Title” from the drop-down menu and accept the default Ascending. Do not click Next yet. 7. Click on Summary Options. Assume that we want the Avg., Min, and Max values calculated for “Book Price”, so select the appropriate boxes. Accept the default “Show Detail and Summary” by clicking OK; now click Next. 2|Page of 5

8. Accept the default “Stepped Layout”, “Portrait Orientation”, and “Adjust”; Click Next. 9. Select the Concourse style then click Next. 10. Type “CS BOOKS REPORT” as the report title; preview the report by clicking Finish. 11. Check out the report. Not bad, but it needs a little work.  Click Close Print Preview, then select the Home tab, then select Design View. 12. Change the average, maximum, and minimum prices to Currency format (if needed). In order to check, click “=Avg. (Book Price)” box. Then right-click, select Properties, select Format, click on the drop-down menu on the Format line, and select Currency. Repeat this process for maximum and minimum prices.

3|Page of 5

13. Spell out the words Average, Minimum, and Maximum at the left of these three lines. Click twice on a box until the cursor is blinking within the box, then type in the new label. Do this for all three lines. 14. Hide the Summary for the “Book Type = CS” line. Click on this box, then rightclick, select Properties, select Format and choose No on the Visible line. 15. Lastly, make the top heading read COMPUTER SCIENCE BOOKS. Use the same approach you used in Step 13 above. 16. Click on View, then Print Preview, and print out your new report. A First Taste of Form Design: Adding a Command Button The wizards make many tasks in Access 2013, including form design, quite easy. We will add a command button to a data entry form for adding data to the Book table. This button, when pushed, will let us find the record corresponding to a particular book title. 1. Click on the Create tab, and then select Form. 2. Click on the Book table from the left side bar and, from the Create tab, under More Forms, select Split Form, and then put the table in Design View.

3. Select the Design tab. Then select and click on the top of the table. A button that says Command with a number next to it should appear along with a pop-up box. Select the Record Navigation category and the Find Record action, and then click Next.

4|Page of 5

4. Let’s put text on our button reading (in caps) FIND BOOK. Click Next. 5. Give the new button a name like “Find Book Button,” then click Finish. 6. To try the button out, switch to Form View. Place the cursor on the “Book Title” field, and then click on your button. Type in the title of the book you wish to find.

5|Page of 5...