Mt. Hood Community College BT118 Records Management ’07‘08 Instructor: M. Heckman ·
· ·
All student files should be saved on your Jump Drive. You'll have these files to take with you to work outside of class, or for review after the class. At school, this drive is generally drive E:\, but may have a different letter assignment on other computers. Set your default drive; click on > Office Button >Access Options > Change Default. All assignments should be labeled (your name, page and step number), placed in order and stapled before submission. Generally, you will submit your assignments printed out on paper, and at the end you may be asked to provide the electronic files – so save each “step” along the way. Watch the tips below for information on when and what to print. Most required printouts have samples available in the STC (Software Training Center, AC2610) – you can compare your printouts with the “key” before submitting them.
2
Project
: Querying a Database
Purpose: You'll explore queries in more depth and complexity, including queries with calculations and crosstabs, sorting and joining tables, creating a report from a query will expand your query expertise.
c
Section
Grading: Complete and accurate work on this section – will earn you a “C” grade (7080% of the available points) on your Access assignment Project 2.
From the Text: READ and do all work on pages AC74 thru AC127 NOTES (I'd recommend you mark these before you begin): q Pg. AC76 Pay particular attention to the Query Design Guidelines – this is the general set of steps for most any query creation. q Pg. AC82 Step #4 PRINT the query results for Recruiter #24, to turn in. q Pg. AC89 Step #3 BEFORE moving on, set the query for cities that start with “Be”, and PRINT the resulting view. q Pg. AC95 Step #1 BEFORE moving on, PRINT the resulting view of the query Recruiter Number 21 AND Amount Paid >20000. q Pg. AC96 Step #2 BEFORE moving on, PRINT the resulting view of the query Recruiter Number 21 OR Amount Paid >20000. q Pg. AC97 Pay particular attention to the Sorting terminology and “how to determine whether special order is required”. q Pg. 101 Step #3 BEFORE moving on, PRINT the unique records of the city query (ascending).
Ó 2005 M.Heckman. All Rights Reserved
1
BT118 Access
Mt. Hood Community College
q Pg. AC102 Step #2 BEFORE moving on, PRINT the resulting multiplesort view (Recruiter Number, ascending, and Amount Paid, ascending). q Pg. AC103 Step #3 BEFORE moving on, PRINT the resulting Top 5 records of the multiplesort view (Recruiter Number, ascending, and Amount Paid, ascending).
q Pg. AC104105 Pay particular attention to the Join Table information and “determining whether more than one table is required”. q Pg. AC105 Step #1 BEFORE starting, open the Client table in design view – and, if necessary, change the recruiter number field name to “Recruiter Number” {remember the field names, and type, must match in the joining field – to create the join line}. q Pg. AC107 Step #7 PRINT the Query Results of the joined tables, to turn in. q Pg. AC111 Step #1 PRINT THIS NEW REPORT, ‘RecruiterClient Report’, to turn in. q Pg. AC112 Step #2 BEFORE moving on, PRINT this Query, ‘RecruiterClient Query w/Amt Paid >20000’, to turn in. q Pg. AC113 Pay particular attention to the Calculations information and “determining whether calculations are required”. q Pg. AC117 Step #2 BEFORE moving on, PRINT this new Query, with a calculated amount and new caption, to turn in. q Pg. AC119 Step #4 BEFORE moving on, PRINT this new Query, showing the single average of Amount Paid from all of the records, to turn in. q Pg. AC121 Step #1 BEFORE moving on, PRINT this new Query, showing the single average of Amount Paid from records where Recruiter number = 21, to turn in. q Pg. AC122 Step #2 BEFORE moving on, PRINT this new Query, showing the average, of Amount Paid, for each recruiter, to turn in. NOTE: If your results show the Average of ALL, click on the down arrow next to the Recruiter Number tab, and deselect “all” and “blank” to show averages for only Recruiter numbers 21, 24 and 27. q Pg. AC122 Pay particular attention to the Crosstab Queries information, to be sure you understand the concepts of the goal, before getting started. q Pg. AC125 Step #7 BEFORE moving on, PRINT this Crosstab Query, showing the total amounts and recruiter breakdown for each city, to turn in.
From the Text: Learn It Online: Flashcards & Practice Test on pages AC128 (requires Internet access) q Pg. AC128 Learn It Online Flash Cards Read and follow instructions at the top of the LIT (Learn It Online) section. Read and follow the instructions as described. Out of 20 cards, you must get 15 correct (75%). If you don’t, you should click Replay and repeat the exercise. PRINT(File > Print) before you close your screen – or you will need to repeat the exercise. Label and turn in this printout. Ó 2005 M.Heckman. All Rights Reserved
2
BT118 Access
Mt. Hood Community College
q Pg. AC128 Learn It Online Practice Test Read and follow instructions. Take the test – there are 20 questions. At the bottom of your test, enter your Name and select Grade to check your test. If you miss more than 4, “Take another test” until you have correctly answered 16 or more of the questions. Print (File > Print) before you close your screen (or you’ll need to take the test AGAIN) – you’ll turn in this printout.
From the Text:
Apply Your Knowledge on pages AC128 q Pg. AC128 Apply Your Knowledge: The Bike Delivers database, from Project 1. Follow the instructions in the book to modify the database, and create queries and reports as requested. q Pg. AC128 Step #1 PRINT the Customer table simple query, to turn in. q Pg. AC128 Step #2 After creating and saving the Courier Parameter Query, RUN it for Courier Number 102, PRINT the resulting query, to turn in. q Pg. AC128 Step #3 Remember that all courier’s should be listed, even if they have no customers (hint: right clicking on the join line, will open the join properties dialog.) PRINT the resulting ‘CourierCustomer Query’, to turn in. q Pg. AC128 Step #4 PRINT the CourierCustomer Query Report, to turn in. Complete and accurate work to this point – will earn you a “C” grade (7080% of the available points) on your Access assignment Project 1. SUMMARY OF PAGES TO TURN IN: 1. Recruiter number 24 Query, page AC82 2. Client Table query for cities “Be*”, page AC89 3. Client Table query Recruiter Number 21 AND Amount Paid >20000, page AC95 4. Client Table query Recruiter Number 21 OR Amount Paid >20000, page AC96 5. Query City Ascending No duplicates, page AC101 6. Multisort (Recruit #, Amount Pd), page AC102 7. Multisort (Top 5), page AC103 8. Query results from joined tables, page AC107 9. RecruiterClient Report, page AC111 10. RecruiterClient Query w/Amt Paid >20000, page AC112 11. RecruiterClient Query w/calculation & new captions, page AC117 12. Average of Amount Paid, page AC118 13. Average of Amount Paid, where Recruiter number =21, page 121 14. Average of Amount Paid by Recruiter number, page 122 15. Crosstab Query, page 125 16. Flashcard printout, showing a score of 75% or more, page AC128 17. Practice Test printout, showing a score of 15 or more correct, page AC128 18. Customer Simple Query, page 128 19. Courier parameter Query for courier #102, page AC128 20. CourierCustomer Query, page AC128 21. CourierCustomer Report, page AC128
Ó 2005 M.Heckman. All Rights Reserved
3
BT118 Access
2
Project
Mt. Hood Community College
: Querying a Database continued
b
Section
Grading: Complete and accurate work on this section – will earn you a “B” grade (8090% of the available points) on your Access assignment Project 2.
From the Text: Do Make It Right, pages AC130131 Keep It Green Database NOTES (I'd recommend you mark these before you begin, and save your database often): q Pg. AC130 Read the query requirements carefully. You should PRINT the query results in each case (2 printouts: Sort query and join table query) once you have resolved the problems. In the database properties, add YOUR NAME as the Author, your course number as the Subject, and in the comment section add the page number AC130. Save on your flash drive and close the database.
From the Text: Do In the Lab #1, pages AC131132 Querying the JMS TechWizards Database NOTES (I'd recommend you mark these before you begin, and save your database often): q Pg. AC131 Step #1 Open the JMS TechWizards database that you created in Chapter 1, and saved on your flash drive. PRINT the Lab 21 Step 1 Query, to turn in. q Pg. AC131 Step #2 PRINT the Lab 21 Step 2, to turn in. q Pg. AC131 Step #3 PRINT the Lab 21 Step 3, to turn in. q Pg. AC131 Step #4 Remember, to set the Unique Query property, you should select the empty cell to the right of the cell, see page 100. PRINT the Lab 21 Step 4, to turn in. q Pg. AC131 Step #5 PRINT the ClientCity Query, to turn in. q Pg. AC132 Step #6 PRINT the Lab 21 Step 6, to turn in. q Pg. AC132 Step #7 Remember that all technicians should be listed, even if they have no clients (hint: right clicking on the join line, will open the join properties dialog.) PRINT the resulting ‘TechnicianClient Query’, to turn in. q Pg. AC132 Step #8 Review AC114115 for a refresher on adding a calculation. PRINT the Lab 21 Step 8, to turn in. q Pg. AC132 Step #9 Remember use the Totals button (S) to add the average. PRINT the Lab 21 Step 9, to turn in. q Pg. AC132 Step #10 This will take a little figuring – remember, what you are trying to do is to display the count of Clients (numbers or names) where the Technician Number is 23. PRINT the Lab 21 Step 10, to turn in. q Pg. AC132 Step #11 This step will take the use of Grouping, for a quick refresher see page AC121. PRINT the Lab 21 Step 11, to turn in. Ó 2005 M.Heckman. All Rights Reserved
4
BT118 Access
Mt. Hood Community College
q Pg. AC132 Step #12 This crosstab query can use the Query Wizard for Crosstabs; for a quick refresher see page AC122125. PRINT the CityTechnician Crosstab, to turn in. q Pg. AC132 Step #13 Be sure to save this database, on your flash drive, for future use.
From the Text: Do In the Lab #3, pages AC134 Querying the Ada Beauty Supply Database NOTES (I'd recommend you mark these before you begin, and save your database often): q Pg. AC134 Instructions: Use the database created in Project 1 as the Ada Beauty Supply database. q q q q q
q q q q q q
Pg. AC134 Part #1a PRINT the Customer with name starting with “C” Query, to turn in. Pg. AC134 Part #1b PRINT the Customer in “Devon” Query, to turn in. Pg. AC134 Part #1c PRINT the Customer with balance=0 Query, to turn in. Pg. AC134 Part #1d PRINT the Customer with amount due =200 AND amount paid 200 AND amount pd