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, unless you are specifically told to do so. Never rebuild the Database. Just keep working on your EXISTING DB. DO NOT keep saving and renaming it. It is a CUMULATIVE set of operations.
1. You will be creating 2 TABLES in a SINGLE Database file. Download the 2 spreadsheet files from my website. Give your fields the EXACT SAME NAMES as the columns in the spreadsheets!!! Enter the data spelled exactly the same as in the book. Do NOT abbreviate.
2. When the book says “Create a query”, it means you are required to print the results (an ordinary Print command for the table/datasheet view which appears when you run the query). The only thing you hand in are printed results. NEVER "submit" the actual database.
3. Make sure you create/save your database on your H: drive so you can get to it from anywhere on or off campus. This also helps if you have to show it to me if you have a serious problem.
Make the following changes:
a. 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-2, and beyond.
b. Item table fields: Item Number (text), Description (25 characters), On Hand (integer), Cost(currency), Selling Price (currency), Supplier Code (2 characters)
c. Supplier table fields: Supplier Code (text, 2 characters), Supplier Name (30 characters), Telephone Number (text)
d. Any report style is OK.
e. CHECK YOUR SPELLING! The attributes of the Supplier Code field MUST match in both tables, including its exact spelling, size and type.
f. In the “Item” table:
“SD” Supplier code to your initials (first & last) on all records where it
(If your initials are SD, reverse your initials.
This will be called your “code” in later instructions.
· Add a record in the Item table as follows:
Item number: 9500
Description: XXYY's Furnace Filters (where YY is YOUR “code”). Keep the XX in front of it.
On Hand: pick a number from 5-9
Base Cost: Pick a cost from $6.00 to $10.00 (inclusive)
Wholesale Price: $2.00 above your Base Cost
Supplier Code: Your “code” (your initials as used instead of SD above)
g. Make sure the datasheet view shows ALL the data (no fields being cutoff).
h. Step 7: Use the Query Wizard
i. Step 8: Use the Form Wizard
j. Step 9: Use the Report Wizard.
You are NOT permitted to use the Wizards after this lab
What to print:
· Normal view of both tables (just open each table and click on Print)
· Properties (design view) of both tables (so we can see the key symbol on your key fields)
o open each table in Design View
o click on item number then move your mouse pointer to the RIGHT over to the empty, 3rd column, but do NOT click there.
o press the “Print Screen” key (In my instructions, this is known as the “Special Print Method” or sometimes “Special Method”)
o open Word for a new file and click on Paste
o print the Word file and exit without saving the Word file.
NOTE: EACH Supplier Code MUST BE UNIQUE within the Supplier Table (that is, you can only use each Supplier Code on 1 record in that table).
CAUTION: You should only have 3 records in the Supplier table, one for each Supplier. The definition of the Supplier Code field in Design view in the Supplier table should be set to:
o DUPLICATES ALLOWED (NO)
o Data type: Text, Size = 2
If your table does not fit these rules, you must fix your tables. The Supplier table should ONLY have Supplier information. It MUST NOT HAVE ANY ITEM INFORMATION!!!
For all query steps:
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 “SD” as a criterion, substitute your code 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 instructions 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.
When saving each query, be sure to include the phrase "Lab 2-2" in front of the Step #. Later labs will have queries and similar step numbers.
All the steps EXCEPT step 8 & 9 use ONLY the Item table.
After designing each query, RUN IT. If it looks good, you can click the "x" at the far right of the screen in line with the query tab. You will be asked if you want to save it. Click "Yes", then enter the name for the step.
o Click on the Description field: change it to a size of 30. Use “Special Print” to print this screen. Label it Step 3a.
o Click on the new Item Type field, then click on the “Lookup” tab in the properties window at the bottom of the screen. Use “Special Print” and print this screen. Label it Step 3b
o Click on “On Hand. Use “Special Print” and print its properties.
o Use the database you created in Access 1-2, as modified in 2-2 and 3-2. (In other words, you must correctly complete ALL of 1-2, 2-2 and 3-2 BEFORE doing 4-2.) Make sure you have the “1” and “∞” symbols showing in your relationships (created in lab 3-2). Click the “relationships” button, select the 2 tables and see if the proper symbols are there on the connecting line. PRINT THIS VIEW (Database Tools/Relationships/Print Relationship Report). Label this as Step 0. We will use it as a reference.
o Step 1: Just open your database as modified in labs 2-2 and 3-2!
o Step 2: Start by using the Form Wizard. Add the fields shown on pg260 AND the Supplier Name field from the Supplier table in the Wizard, specify you want to view the form by Item Table.
o Step 3: Use the Design Wizard to START your report design, (you MUST use the Report Design View after the first time you create the report) using these Wizard options:
o Step 3 continued: Now make these changes in Design View:
o Step 4: Create the filter and print the filtered report.
o Step 5: Print the report.
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 listed in my “downloads” web page.
o When creating your boiler-plate, watch the placement of the punctuation and spaces.
o 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.)