COIS-3380 Assignment #5 – XLM J | Complete Solution

McMurry University COIS-3380, Management Info Sys
Summer 2015 (Mr. Brozovic)
Assignment #5 – XLM J

Complete the step-by-step Access exercise in XLM J, following the additional instructions in this
document. You may want to view the author’s lecture slides which provide a number of extra
pointers. The PowerPoint lecture can be downloaded from the textbook’s Web site. However,
do NOT download and submit the author's solution for XLM J – it has a number of differences.
Doing so will result in a grade of 0 for this assignment!

The following items supplement the step-by-step instructions from textbook XLM J:
• Page 480 – Bill of Material table: Decimal places property for Concrete Type must be 0.
Field size property for Unit must be single instead of long integer and decimal places must
be auto. Set all 3 fields as required.

• Page 481 – define all remaining relations (table designs) using info from Figure J.8. Ignore the “optional for our purposes” comments. Set all integrity constraints as stated with the following additional guidance. For the Customer Phone field, set the field size to 10 and then set the input mask by clicking the … button at the right side of the input mask property box to bring up the Input Mask Wizard and select Phone Number. For the Raw Material ID fields in both the Raw Material and the Bill of Material tables, set the format property to > which will automatically convert lowercase letters to uppercase.

• Page 483 – after adding all relations to the relationship palette and before adding any relationships, rearrange the tables as shown in Figure J.11 by dragging their title bars around. Also, resize the table windows so that all field names are fully displayed.

• Page 484 – define all relationships as shown in Figure J.11. Mistakes can be deleted or edited by right-clicking on the relationship link. Adjust table positions in the relationship pane as needed to clearly show the relationships without lines crossing each other or table boxes.

• Pages 485-487 – enter all remaining data from Figure J.1 paying attention to the instructions in the textbook for populating the tables with data. After entering the data into a table, “best-fit” each column in the datasheet view by double-clicking the right border of the field name cell (you should have a double-headed arrow pointer) and save the changes to the layout when you close the table. Add one more Customer record with customer data: 9999, “McMurry University”, (325) 793-6824, with your first name or nickname and last name as the Customer primary contact. Add one more Employee record with employee data: 999999999, your last and first name or nickname, and a hire date of 7/7/2014.

• Page 488 – save the simple query with the name suggested.
• Page 489 – to add selection criteria to an existing query, modify a copy of the previously saved query. Right-click the desired query name and select copy. Then right-click in same area and select paste. Rename copy of query by adding “with Criteria” to end of existing name. Right-click new copy and select Design View. Modify with criteria and save. Close and reopen query and notice that criteria has changed to “400 or >400” on a single line. Another equivalent criteria would be to enter “>=400”.
McMurry University COIS-3380, Management Info Sys Summer 2015 (Mr. Brozovic)

• Page 490 – In the query table display area, resize all tables to properly display all field names and rearrange the tables to show the relationships without lines crossing each other or tables. Save the query with the name “Orders”.

• Pages 490-491 – Save the practice queries with names “Practice 1” … “Practice 8”. Resize and rearrange tables as in previous step.

• Pages 492-493 – Save the report with the recommended name then modify the report to move the Customer name column over so that it is aligned under the word “Report”. Save the changes to the report when prompted.

• Page 494 – Create the described query and save it with the recommended name. Resize and rearrange tables as in previous steps in order to properly view field names and relationships. Your result should display 18 lines.

• Pages 494-496 – report orientation (step 8) must be landscape (not portrait). The report style in step 9 is a left-over from Access 2007 and does not exist in Access 2010. Instead, you would apply a Theme while in design view, but is not required for this exercise – the default theme is “Office”.
• Pages 496-499 – Report design view – adjust items as described in text and below.
o See the sample report (Assign5XLM-JReport.pdf) from the Moodle assignment page
for correct version of “revised” report. It’s much easier to view than the author’s
textbook figures.

o General instructions: To align and resize items, use shift+click to select multiple items
then use options from the arrange menu. For centered items to align properly with
their headings, the heading and data items must be aligned together and should be
same width.

o Page & Concrete Type headers – make sure column labels and data are fully displayed
when you print preview the report. Easiest way to get the 2-line headers is to use
shift-enter where you want the line to break. Center headers and details only for
“Concrete Type”, “Unit”, and the two “ID” fields.

o Concrete Type footer – move unit subtotal under and centered with corresponding
data. It should be red and bold along with its label which is repositioned.
o Move items to top of their report sections and then “shrink” each section by moving
its bottom edge up to the items – report must fit on one page with the default margins
of ¼ inch.

• Page 499 – Save basic form as “Order Form” when prompted for a name.