Dart Online Services 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, when you go to a new lab. You may, of course, correct mistakes. The point is that the lab instructions for each lab are based on everything you did in the previous lab.

Notes:

1.   You will be creating 2 TABLES in a SINGLE Database file. Read the instructions carefully and use the names shown in the book for your table names.

2.   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.

3.   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.   Open Access. 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.   After you add the fields in the picture, for EACH table remove the primary key field named ID and make the 1st field in each picture become the primary key for that table. To do this:

a. Open Design for the table

b. Click on the field that has the key symbol on its left (it's name will be ID)

c. Click on the key icon on the ribbon. The key symbol will disappear from in front of the field.

d. Now you can delete the ID field.

e. Now click on the field that is supposed to be the key

f.  Click again on the key icon on the ribbon. The key symbol will appear in front of the new field

3.   Be sure your text fields are CHANGED from 255 characters to the specified size (see next instruction).

4.   In the Customer Data table,

o Be sure Customer Name is 25 characters long.

o Customer Number & Service Rep Number are not numbers (they are TEXT fields).

o Postal Code is a 5 character TEXT field. That's because zip codes beginning with zero would lose the zero if they were numeric.

o Be sure to change the alignment for Postal Code to "right", so it looks like the picture.

o Be sure the "Service Rep Number" field is 2 characters. You MUST change it from the default (255) in BOTH tables.

1.   In the "Service Rep Data" table, add a record with your name as the rep with the following values:

Service Rep Number:   40

Monthly Salary:     $4,000

Remaining data for THIS entry is required, but up to you.

2.   In the "Customer Data" table, add the following record (substitute your initials for "ZZ" in both places):

Customer
Number

Customer Name

Street

City

State

Postal
Code

Balance

Amount
Paid

Service Rep
Number

XXZZ

ZZ's Styles

999 9th

Richmond

VA

23233

$500.00

$1350.00

40

3.   Step 10 clarifications: Use the Report Wizard. Choose the specified fields. Select all defaults. IGNORE the requirement to create a total for the Balance field. For Summary Options, select "Summary Only".

4.   Print the 2 tables in TWO views: Datasheet view and Design View (use the special print to do this).  There will be 6 pages:

    1. 2 ordinary table views
    2. 1 reports
    3. 1 query result
    4. 2 Design Views  (screen shots)

Access 2-1

For EVERY Query there will be TWO print-outs:

·      Use the Special Method to print the entire Query Design View, as well as the query results view! The Query Design View is printed 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 Service Rep Number. It will look SIMILAR to the figure below. Note that the figure for this example was taken from a DIFFERENT database, so the field names do NOT match your tables. BUT, note that the arrow starts and ends at the same name in the 2 tables. Your's must do the same. When you first create the connection, you might not have the 1 and infinity (∞) symbols. They WILL exist after Access lab 3 (if you follow the instructions).

acc-db-relationships

If the line does NOT appear, DO THIS: Click (left button) on the Service Rep Number in one list and drag it to the other list. If the line does not appear NOW, your Service Rep Number field definitions (size & type) do not match, so go back to the Table View for both tables and make them match before proceeding with this lab.

"Create a query" means to build the query using the QBE grid and run the query to get the results. Be sure to save each query and name it with its step number as discussed in the book. So the first query you save will be named "Step-1 Query" or "Query Step-1". You may optionally add the text "Lab 2-1" as part of the name. Steps 6, 7, & 8 also require you to create a query, POSSIBLY starting from an existing query.

Be sure the resulting table for each query includes ONLY those fields specified in the query plus any changes noted below. In steps with multiple fields involved, the required fields are shown 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 the addenda.

·      Step 2: The results will include the record with your initials in it.

·      Step 3: as part of your Query Design, include any Customer Number starting with X. This will be an "or" query. Be sure to show the entire Customer Name field.

·      Step 4: right-click in the proper Sort cell and select "Totals". You only need the Customer Data table. The book explains how to avoid duplicates.

·      Step 7:

1. In your query design view, right click EXACTLY on the line between the 2 tables.

2. Click on &lquot;Join properties&rquot;.

3. Select the option that says "include ALL Service Rep Data and ONLY those items from Customer data where the joined fields match" or similar wording.

·      Step 8: Do not DELETE the table, just REMOVE it from the Query Design. (Right click on it and click on "remove"). After saving the modified query, run it and print the results and the Design View. The phrase, "total of Balance and Amount Paid" means you need a calculated field to get this column in your results

·      Step 10: also show the Service Rep Number field.

·      Step 11: also show each Service Rep's Last Name field. (You will have to include the Service Rep table to do this.)

·      Step 12: your results should look like those in the picture, with Service Rep 45 (yourself) included. Follow the steps in the book for creating a Crosstab Query using the Crosstab Wizard! Building it in Design View by hand is NOT intuitive.

·      Step 13: do NOT send us your DB.

Access 3-1 (Dart Online Services)

·      Step 2: See the instructions on pg 156 for using the Lookup Wizard. After saving, use the Special Method to print the Design view of the Table.

·      Step 3: Print the Client Data table normally (i.e.; NOT using the Special Method) so we can see the changes.

·      Step 4: AFTER part d, print the Design View of the Client table.

·        Step 5: Do not use Filter by Form. Use the basic filter button on the home tab ribbon. Set both filters. You should only see one record.  PRINT the results. Do NOT DELETE and do NOT SAVE the changes to the table. Use special print. To undo the filter and see all the records from the table again click the highlighted “toggle filter” button

·      Step 6: For the 2 cities Kyle, that you didn’t delete (not shown in the book’s picture) check off Arch. For your added XX record, list the services needed as Data and Drec.

·      Step 9: skip this step

·      Step 11: print the relationship report. Be sure you have the 1 and infinity symbols.

·      Step 12: print the 2 tables in datasheet (list) view

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 ALL of 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.

·      Be sure your titles on BOTH reports look like the ones in the book (for example, on report 1, the word "Customer" is above the word "Type".

·      Step 1: Follow the instructions on pg 208, 210, etc. (in the 2013 SC book) to create the grouping by Type. To add the Amount Paid field, FIRST click on the Service Rep Number field name on the top, then HOLDING the CTRL key, click AGAIN on one of the Service Rep Numbers. You will see a golden box around these fields. At the top left, there will be a "field move" cursor control. Use it to move the fields to the right to make room for the Amount Paid field. Now click on the Design tab on the menu bar, click on "Add Existing Fields", then double-click on the Amount Paid field. It will be inserted "somewhere" on your report with a gold border. Just move it to the newly created empty space.

MODIFY your report to get the titles ("Customer Type", "Customer Number", etc.) to take up 2 rows, as shown in Fig. 4-83.

·      Step 2: Use the Wizard to START the report design of Fig. 4-82, using the Wizard options: Sort by Customer Number, Layout=Blocked, Landscape orientation. Allow the default to fit fields to the page. When the Wizard is finished, switch to the Design View. Find "Themes" on the ribbon and select the "Integral" theme.

Now apply the following changes:

o Remove the text ("Summary for ..." and the formula next to it) on the group sum and grand totals lines. Be sure there is a formula that gives the sum of the balance as shown, and remove the word "Sum" on the left.

o To unlock fields (if necessary) (in the Page Header) from column entries,

§ right-click on the field in the Page Header row,

§ then click on the Layout Tab of the pull-down menu,

§ then click on Remove.

You may have to do this with all the balance fields and the grand total field in the Report Footer row.

o 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.

o Remove the extra space below the report title.

o Your report should have ALL columns completely lined up as in the picture in the book.

o Ignore the borders that partially surround the fields in the figure.

o The text "Grand Total" should be at the far left in the Service Rep Number column on the last sum line. Any font is OK. To get an actual value to go with the "Grand Total" you will have to insert a text-box with a computed value (a formula). Print the report in the Integral style.

o Your report should fit on 1 page- Go to design view and change the height of the Report Header to make the report fit 1 page

 

·      Step 3 (Creating a Form):

o Use the wizard to START creating your form. Use the option: columnar

o Remove the Client Id field and its label field from the form.

o Insert a Combo Box where the Client Id field used to be. (Your form will look almost the same as the one in the book, with the addition of the drop-down arrow for the Combo Box.) Make the Combo Box field content use these options in the Wizard:

1.     "Find a record in the database based on what I type in the combo box".

2.     "Client Data Table"

3.     " Client Id "

4.     no sorting specified (if asked) - just click "next"

5.     no changes - just click "next"

If you have problems with the creating the Combo Box, remember that when you create the Combo Box, it starts out "unbound", which means it is not "connected" to a field. To connect or "bind" it, do this:

6.     Right click on the part of the box that says "unbound".

7.     At the bottom of the menu, click on "properties".

8.     In the properties on the right of your screen, click on the Data tab

9.     Click inside Control Source and select the proper field from the dropdown box.

Your personal record can be selected when you open the datasheet view of the form.

o Modify the label-boxes as shown in Fig. 4-83.

o Click the arrow-button on your form and select YOUR record. Print the Design View of the form using the Special Print Screen Method so we can see the button created by your combo box (Customer Number field).
Now switch to the datasheet view of the form.
Be sure the record with your Deli (where ZZ is your initials as in Access lab 1-1) is showing instead of the item specified in the book. Print this copy of the form. Do not print one for every record. There is a PENALTY for printing all the records in normal view or for printing any records that do not show the button.

There will be 4 pages printed: 2 reports (normal print) and 2 form (using the special print for BOTH datasheet view and design view)

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.