Hockey 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-2:
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. The “Metro” report style should be used.
3. Item table fields: Item Number, Description, On Hand, Cost, Selling Price, Supplier Code
4. Supplier table fields: Supplier Code, Supplier Name, Telephone Number
5. CHECK YOUR SPELLING! The attributes of the Supplier Code field MUST match in both tables, including its spelling, size and type.
6. In the "Item" table, change the "AC" vendor code to your initials on all records where it is used. This will be called "your code" in later instructions.
7. In the "Supplier's" table,
  1. substitute your code for the "AC" code.
  2. change "Ace" to your last name, with the apostrophe-s at the end
8. If and ONLY if your initials are LG or MN, then
  1. change the length of the supplier code to 4 in BOTH tables.
  2. in BOTH tables, put a "-1' after your initials in all places where the original "AC" supplier code was used.
    For example if your initials are MN, then the AC code becomes MN-1 and there will be TWO suppliers whose codes START with "MN".
NOTE: EACH Supplier Code MUST BE UNIQUE within the Supplier Table (that is, you can only use a Supplier Code on 1 record in that table).
CAUTION: You should only have 3 records in the Supplier table, one for each Supplier. And the definition of the Supplier Code field in Design view should show that field as:
DUPLICATES ALLOWED (NO).
If your table does not fit these rules, you must fix your tables. The Supplier table should ONLY have SUPPLIER information. It should NOT HAVE ANY ITEM INFORMATION!!!
Access 2-2:
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 Supplier Code.
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 Supplier Code field definitions do not match, so go back to the Table View for both tables and make them match before proceeding with Lab 2.
For any query using "AC" as a criterion, substitute your initials as used in Lab 1.
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 in the instruxtions in the book, 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 in any of the additional instructions below.
- "Create" means perform the query and PRINT the results.
- Step 7: remember to use YOUR code.
- Step 8: this means sort by item code within supplier code, as done in figure 2-88.
- Step 9: the report in the book labels a column "Hand". It SHOULD be "On Hand".
- For steps 4, 5, 6, 7, 8, 10, 11, 12 submit the "special print", showing your QBE screen. Label these prints with the same step numbers as the regular prints and keep these special prints with their regular prints.
Access 3-2:
- Step 2: print the Item Table
- Step 3b,c & d: Print the Design View of the Item table (special print). You do NOT have to print the table itself.
- Step 5: print the results and the Special Print view showing the query rules
- Step 8: print the Item Table
- Step 9: print the Item table as displayed by the Advanced Filter. You should be abe to do an ordinary print. Note that ALL fields of the table WILL be present, not just the ones specified in this step.
- Step 11: Print the item table
- Step 12: See the book for instructions on how to enforce referential integrity. When you create the relationships, be sure to click on "Cascade the Update". If you don't see a 1 at the Suppliers end of the line and an ∞ at the Item end, then you haven't done it correctly or there is a problem with your tables. Be sure that the field names are spelled the same way in both tables (Design View) and have the same size in both. Correct any errors and re-try. Use the Special Method to print this view of the relationships.
Access 4-2:
- Use the database you created in Access 1-2, as modified in 2-2 and 3-2. (In other words, you must complete 2-2 and 3-2 BEFORE doing 4-2.) 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 Item Number field font to Rockwell.
  - Remove the text (not the formulas) on the partial sum and totals lines
  - To unlock any field (in the Report Header) from its neighbors,
    - click on the field in the Report Header row,
    - then click on the Layout Tab of the ribbon,
    - then click on Remove in the ribbon.
  - Do the same with that field in the Detail row of the Report Design screen.
  - Now slide the field(s) to the desired positions, 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.
  - For the borders that partially surround the numbers in the figure, just insert a "summation line" as in ordinary arithmetic, like I did inthe example in my book. That is, don't worry about the vertical lines between numbers.
  - Create a "Grand Total" row and insert formulas that calculate the totals of Cost and Selling Price.
- Step 2: Instead of "Red Bold", use Bold Underlined. Be sure the headings that take 2 rows in the picture, take 2 rows in your print.
- Step 3:
  - Ignore the scroll buttons in the Item Number field, they are not there from the Wizard and the book does not describe how to get them or why they are there.
  - Add the Supplier's Name to the right of the Item Number. Open the form view in Normal View, and use the Special Print Screen Method so we can see the button on your combo box (the Item Type field). Be sure the record with your name 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 here.
- When creating your boiler-plate, watch the placement of the punctuation and spaces.
- Be sure your fields line up properly as in the example, on EVERY form letter printed. If they don't, you did not use the correct technique (I lectured on this during word processing.)