JMS Techwizards Database Addenda for Fall

For ALL Access labs: If the instructions add records, do not remove them at the end of the lab. If records are deleted by the instructions, do not replace them. For each Access lab after the first one, continue working from the results of the previous Access lab. That is, never UNDO any changes to the database made by the instructions or this addenda.

Notes:

You will be creating 2 TABLES in a SINGLE Database file. Read the instructions carefully and use the proper names for your tables.
When the book says "Create a query", it means you are required to print the results (an ordinary Print command for the table view which appears when you run the query). The only thing you hand in are printed results. Do NOT submit the actual database.
Make sure you save your database on your H: drive so you can get to it from anywhere on or off campus.

Access 1-1:
Make the following changes:
1. Click on the Office button. Click on Access Options at the bottom. Click on Object Designers. In the Query section, click on Enable Autojoin. Click OK. You will need this for Access 2-1, and beyond.
2. In the "Technician Data" table, add a record with your name and the following data:
  
  Technician Number
  
  25
  
  Hourly Rate
  any amount from $25 to $35
  
  YTD Earnings
  any number from $8,000 and $9,999
  
  Remaining data is required, but up to you.
3. In the "Client" table, add the following record (substitute your initials for "ZZ" in both places):
  
  Client
  Number Client Name Street City State Postal
  Code Telephone Number Billed Paid Technician
  Number
  
  XXZZ ZZ's Deli 999 9th Anderson TX 78077 512-555-1212 $500.00 $350.00 25
  
  Note: Technician #32 will get a client in Lab 3-1
Access 2-1:
For EVERY Query:
- Use the Special Method to print the entire Query Design View. This is done using the special print "hint" on the course slides.
- Also print (via normal File/Print or the Print icon) the query results (what you get when you RUN the Query).
For queries that use BOTH tables: when you select the 2 tables, a line "should" appear, connecting them by Technician Number.
If it does NOT appear, DO THIS: Click (left button) on the Technician Number in one list and drag it to the other list. If the line does not appear NOW, your Technician Number field definitions do not match, so go back to the Table View for both tables and make them match before proceeding with Lab 2.
Be sure the resulting table for each query includes ONLY those fields specified in the query (or the changes noted below). In steps with multiple fields involved, the required fields are shown with a capitalized first letter. Other fields must NOT be shown in the result, even if they are part of the query criteria, EXCEPT as specified below:
- Step 2: use Kingston instead of Liberty Corner and $200 instead of $500.
- Step 3: as part of your Query Design, include any Client Number starting with XX and include the Technician's Last Name field.
- Step 4: right-click in the proper Sort cell and select "Totals". You only need the Client Data table. The book explains how to avoid duplicates.
- Step 5: include Technician's Last Name field.
- Step 8: the calculated field is to be shown as "Hours worked" in the results table and be sure to specify "fixed" for its properties (the book says "integer").
- Step 10: also show the Technician Number field.
- Step 11: also show the Technician's Last Name & Technician Number fields.
- Step 12: your results should look like those in the picture, with technician 25 included (updt 11/18/08).
Access 3-1:
- Step 2: Click on the Lookup tab in the Design View, then use the Special Method to print the Design view of the Table.
- Step 3: Print the Design View of the Query Special Method (BEFORE you run the Query) THEN run the query. When you close the Query the database has already been saved. That is good. We want to keep the changes made by the query. Now print the Client Data table normally (i.e.; NOT using the Special Method).
- Step 4: For each substep, use the special print method to print whatever method you used, so we can see you actually did it that way. Do not print the table until AFTER sub-step d.
- Step 5: Print the normal view of the table.
- Step 8: Print the normal view of the Client Table.
- Step 9: If you don't see a 1 at the Technician end of the line and an ∞ at the Client end, then you haven't done it correctly or there is a problem with your tables. Be sure that the field name, "Technician Number", is spelled the same way in both tables (Design View) and has the same size in both. Correct any errors and re-try. Use the Special Method to print this view of the relationships.
Access 4-1:
- There is no data disk or file to download. Just use the database you created in Access 1-1, as modified in 2-1 and 3-1. (In other words, you must complete 2-1 and 3-1 BEFORE doing 4-1.) Make sure you have the "1" and "infinity" symbols showing in your relationships (created in lab 3-1). Click the "relationships" button, select the 2 tables and see if the proper symbols are there on the connecting line.
- Step 1: Use the Design Wizard to START your report design, using the options: Stepped and Style=Metro. When the Wizard is finished, switch to the Design View and apply the following changes:
  - Change the Client Number field font to Rockwell.
  - Remove the text (not the formulas) on the partial sum and totals lines
  - To unlock the Paid field (in the Page Header) from its neighbors,
    - right-click on the Paid field in the Page Header row,
    - then click on the Layout Tab of the pull-down menu,
    - then click on Remove.
  - Do the same with the Paid field in the Detail row.
  - Now slide these 2 Paid fields about 1/4" to the right, then stretch their left sides to the left a little thus making them wider.
  - Switch to Report View and see if all fields line up in their columns as shown in the report. If not, repeat the above process as needed with the partial sums and totals.
  - Your report should have ALL columns completely lined up as in the picture in the book.
  - Ignore the borders that partially surround the numbers in the figure, but you can get 5 pts extra credit for inserting them all exactly as in the figure.
- Step 2: The text "Grand Total" should be at the far left in the Technician Number column on the last sum line. Any font is OK. Be sure the headings that take 2 rows in the picture, take 2 rows in your print. Print the report in the Metro style. To get an actual value to go with the "Grand Total" you will have to insert a text-box with a computed value.
- Step 3:
  - Ignore the scroll buttons in the Client Number field, they are not there from the Wizard and the book does not describe how to get them or why they are there. BUT, 5 points extra credit if you can get them. Do NOT waste a lot of time trying!
  - Add the Technicians Last Name below Technician Number. Print the form view once, in Normal View, using the Special Print Screen Method so we can see the button on your combo box (the Client Type field). Be sure the record with XXZZ's Deli (where ZZ is your initials as in Access lab 1-1) is showing instead of the item specified in the book. You only need to print this ONE copy of the form. Do not print one for every record.
Integration:
Do NOT put back any records that have been deleted in the course. Do NOT delete any records that were added. In other words, continue working based on the results of what you did in all previous labs. The remainder of the instructions are in a separate file at: Integration.