MISS PROFESSOR ONLY

Page 1 of 7

(18PR) Integrated Project Part II Instructions for BCIS1405 Students ONLY

Last Changed: 8/17/2016 10:10 PM

This part of the project is for BCIS1405 students ONLY

This project is based on Access, Excel, and Word modules integrated together. It is a good

review for the final exam.

Print these instructions for future reference.

Steps Instructions Points

1

Download the project files by clicking on the next link in this module: (18PR) Project Part II DATA.zip folder.

Note: Most Windows versions will automatically download the file to your Downloads folder. Other Windows versions may

require you to designate where you want the file saved.

0

2

Extract the data from the downloaded zipped folder. This will create a folder named: (18PR) Project Part II DATA.

You should see two files within this folder:

 File4Import.xlsx and

 Logo2Copy.wmf

Note: You may see a folder instead of the two files. If so, open the folder.

0

3

Copy the two files listed above, that you just extracted, into your Lastname_Firstname_PROJECT folder.

Note: Recall that you created the Lastname_Firstname_PROJECT folder earlier when you completed the PowerPoint part

of the project. For more information, see (18PR) PPT Project Instructions.

(Note: All files that you create below should be saved into the

Lastname_Firstname_PROJECT folder!!!)

0

ACCESS

View the next 3 links within this module for a PDF sample solution of how your output should look after completing this exercise!

4

Start Access. Create a Blank Database file named Lastname_Firstname_18PR_Access, so that Lastname is

YOUR lastname and Firstname is YOUR firstname. Make sure to Save this database file in the

Lastname_Firstname_PROJECT folder.

1

Page 2 of 7

Steps Instructions Points

5

Close the default Table1 created in the new database file.

Create a new table using the Table Design button. Use the design information below. Name the table: Spokespeople

Field Name Data Type Field Properties

Spokesperson Short Text Field Size: 3

Required: Yes

Set this field as the PRIMARY KEY

Firstname Short Text Field Size: 15

Lastname Short Text Field Size: 25

Career Short Text

Available Yes/No

Gender Short Text Field Size: 1

5

6

Go to Datasheet View of Spokespeople table and enter the following records:

JB1 Jim Brown Football No M

JB2 James Brown Music No M

MC1 Miley Cyrus Music Yes F

MA1 Muhammad Ali Boxing No M

RG1 Ryan Gosling Acting Yes M

WS1 Will Smith Acting No M

Save and Close the Spokespeople table.

2

7

Using Import Excel spreadsheet wizard Import data to create a new Products table. Make sure you choose the

following options in the wizard steps:

 Browse to select File4Import.xlsx file located in Lastname_Firstname_PROJECT folder,

 Select the option for Import the Source Data into a new table in the current database,

 Make sure First Row Contains Column Heading is selected and click Next TWO times,

 Make sure to Choose my own primary key as Product ID and click Next,

 Finish the import to Products table,

 In the final step Do not save the import steps and Close the wizard.

3

8 Open the Products table and make sure all field widths are resized to fit column heading/data. Save and Close the

Products table. 1

9

Create a one-to-many relationship between the Spokesperson field in the Spokespeople table and the

Spokesperson field in the Products table.

Enforce referential integrity between the two tables.

Close the Relationships window, saving the changes.

1

10

Create a Simple Query Using the Query Wizard:

 Create a simple query

 The query should be based on the Spokespeople table (i.e., MAKE sure and change the table under

Tables/Queries to the appropriate table).

 Include the spokesperson's Firstname, Lastname, Career, and Gender fields, in that order.

 Save the query as Music AND Female Query.

 In Design view, set the Criteria to select only those records with whose Career is in Music AND whose Gender

is a Female (F).

 The Gender field should not appear in the query results (make sure the Show check box is not checked for the

2

Page 3 of 7

Steps Instructions Points

Gender field).

 Run, Save and Close the query.

11

Create another simple query based on the Spokespeople table.

 Include the spokesperson's Firstname, Lastname, and Career fields in the query.

 Save the query as Acting OR Music Query.

 In Design view, set the Criteria to select those records with whose Career is in Acting OR Music. (i.e., type

“Acting on Criteria line under Career, and then type “Music” right below it on the or: line)

 Run, Save and Close the query.

2

12

Create another simple query based on the Products table.

 Include Product Name, and Unit Price fields in the query.

 Choose Detail query, and then Save the query as Calculated Query.

 In Design view of the query, create a calculated field called Discount that determines the discount by

multiplying the Unit Price by .08.

 Run and Save the query.

 Modify the format of the calculated field to Currency, with 2 decimal places.

 Display the results in descending order by Discount.

 Run, Save and Close the query.

2

13

Create another simple query based on Spokespeople table and Products table.

 Include Career field from the Spokespeople table, and include Unit Price and Unit Cost fields from the

Products table.

 Choose Detail query, and then Save the query as Totals Query.

 In Design view of the query, click the Totals button.

 Make sure that the Career field is set to Group By in the Totals row.

 Set the Unit Price field to Sum in the Totals row,

 and in the Totals row, set the Unit Cost field to Avg.

 Run the query.

 Resize the fields to show all of the contents (headings/data).

 Save and Close the query.

2

14

Using the Report Wizard, create a new report based on the Spokespeople table.

 Include all fields from Spokespeople table.

 Group by Career.

 Sort the records by Firstname in ascending order.

 Use Stepped layout,

 Landscape orientation,

 and adjust field width so that they fit on one page.

 Save the report as Spokespeople Report.

2

15

Change the title of the report to Your Name Spokespeople Report (Where Your Name is your actual Firstname

Lastname, ex: Mary Smith Spokespeople Report).

 Also, change the report title’s font to Tahoma,

 font color to Purple and

 font size to 28.

2

16

Insert the Logo2Copy.wmf file (located in Lastname_Firstname_PROJECT folder) as a Logo into the

Spokespeople Report.

 Resize the logo to 1” width and 1” height.

 Move the report title just towards the right of the logo so that they are not overlapping with each other (if you

2

Page 4 of 7

Steps Instructions Points

have trouble doing this in Layout View, try Design View)

 Save and Close the report.

17 Using the Form Wizard, create a new form based on the Products table. Include all fields from Products table. Use

Columnar layout. Save the form as Products Form. 2

18

With the Products Form opened add a new record with the following data:

 7 for Product ID,

 Your Fullname for Product Name,

 BCIS for Category,

 1405 for Unit Price,

 11 for Unit Cost, and

 JB2 for Spokesperson.

Save and Close the Products Form.

2

19 Open the Products table and Sort it by Category field in ascending order. Save the change. 1

20

Using Export to Excel spreadsheet wizard, Export data from the Products table into

Lastname_Firstname_18PR_Excel file. Make sure you choose the following options in the wizard steps:

 Browse to select Lastname_Firstname_PROJECT folder,

 Enter Lastname_Firstname_18PR_Excel as the destination file name so that Lastname is YOUR lastname

and Firstname is YOUR firstname,

 Make sure the file format is set to *.xlsx,

 Select the option for Export data with formatting and layout,

 Select the option for Open the destination file…, and

 Click the OK button.

 The Excel file should open. You can now Save and Close the ACCESS DATABASE:

o Do not save the export steps and Close the wizard. Save

Lastname_Firstname_18PR_Access.accdb file, Compact and Repair Database, Close the

database, and Exit Access application.

3

EXCEL

View the next 3 links within this module for a PDF sample solution of how your output should look after completing this exercise!

21

Add the following column headings in the Lastname_Firstname_18PR_Excel file that opens from the above step:

 In cell G1 enter Discount,

 In cell H1 enter Discount Percent,

 In cell I1 enter Discount Cost,

 In cell J1 enter Total Cost, and

 In cell K1 enter Payment as column headings.

1

22 Select the column headings A1:K1 and apply Cell Style of Heading 3. 1

23 Widen the Product Name (column B) to 25, the Discount Percent (column H) and Discount Cost (column I)

columns to 10. 1

Page 5 of 7

24 Select row 1 and Wrap Text to display column headings in H1 and I1 as multiple lines within the cell. 1

25 In cell G2, insert an IF function that displays Discount with a “Y” or “N” if the Unit Cost is less than or equal to 100.

Use the fill handle to copy this formula down to row 8. 5

26

Create a Discount Percent Lookup Table as follows:

 In cell G10 enter 0, in H10 enter 0.1,

 In G11 enter 25, in H11 enter 0.2,

 In G12 enter 50, in H12 enter 0.3,

 In G13 enter 75, in H13 enter 0.4,

 In G14 enter 100, and in H14 enter 0.

1

27

In cell H2, insert a VLOOKUP function that returns the Discount Percent that corresponds with the

 Unit Cost in cell E2.

 The function will refer to the Lookup Table created in G10:H14 and

 return the corresponding Discount Percent value from the second column of the Lookup Table.

 Make sure appropriate cell references (relative/absolute references) are made.

 Use the fill handle to copy this formula down to row 8.

 Set column H to Percent style with 0 decimal places.

5

28

In cell I2 create a formula (NOT A FUNCTION!) to insert the new Discount Cost (which is the Unit Cost multiplied

by Discount Percent).

Use the fill handle to copy this formula down to row 8.

Set Column I to Currency Number Format.

2

29

In cell J2, create a formula (NOT A FUNCTION!) to calculate the Total Cost (which is Unit Cost minus the Discount

Cost).

Use the fill handle to copy this formula down to row 8.

2

30

In cell K2, insert a PMT function to calculate the monthly product Payment

 using an interest Rate of 7% (per year which means 7%/12),

 an Nper loan term of 3 months, and

 refer to the Pv as the Total Cost in J2.

 Make sure that the (Pv) monthly Payment displays as a positive number (i.e., use a negative sign in front of

J2)

 Use the fill handle to copy this formula down to row 8.

5

31

Use appropriate functions to do the following in cells J10:J13:

 In cell J10 enter a function to sum the range J2:J8,

 In cell J11 enter a function to average the cells in the same range,

 In cell J12 enter a function to display the lowest value in the same range, and

 In cell J13 enter a function to display the highest value in the same range.

3

32 Format cells J10:J13 with Accounting format. Use the fill handle to copy the cells J10:J13 to K10:K13 1

33

Change the Page Layout of the Products worksheet so that it:

 Is set to Landscape orientation,

 Will FIT to one Page,

2

Page 6 of 7

 Has a Customer Header with Your name in the left section, Sheet Name in the center section, and Date on

the right section (choose any style but make sure to update automatically), and

 Will print with Gridlines.

34

Insert a 3-D Pie chart using the Product Name data and the Payment data and do the following:

 Move and resize the pie chart so that it is located in cells A10:F30 (you might want to change your zoom

settings so that you can view the entire sheet on one screen),

 Change the Chart Title to Excel Project

 Change the Chart Style to Style 7,

 Add inside end Data Labels.

5

35

Copy the Pie chart created in the above step.

Note: If you had trouble with Excel, do not worry, you can still get partial credit. Make sure and create some type of pie

chart in Excel, and copy that chart into Word.

0

WORD

View the next 3 links within this module for a PDF sample solution of how your output should look after completing this exercise!

36

Open Word application and Paste the chart into the new word document. Save this word document as

Lastname_Firstname_18PR_Word.docx, make sure to save this file in Lastname_Firstname_PROJECT folder

created previously.

Note: If you had trouble with Excel and you are not able to insert the pie chart, then instead of copying the pie chart,

open Word and insert an Online Picture of a “pie chart”, and resize to a height of 3”.

2

37

Insert a Header into the document with Your Name in the left section, today’s date in the center (choose any style but

make sure it is NOT automatically updated) then, tab to the right section and insert Page Number in the Current

Position, choosing Accent Bar 1 style.

1

38 Move your cursor to the end of the document (Ctrl-End) and press Enter key TWICE (two times) and do the following

step. 1

39

At this cursor position set the following Tabs:

 Left Tab at 1”, a Center Tab at 3”, and a Right Tab at 5.5”

Enter the following lines of information at the appropriate tabs:

BCIS1405 Semester Your Name

Red White Blue

With your cursor still at the end of “Blue”, press Enter TWICE.

At this cursor position, Insert a Next Page Section break (go to PAGE LAYOUT tab, click Breaks, and select Next Page

under Section Breaks)

5

40

On the new page inserted from the above step, do the following:

 Change the new page orientation to Landscape (make sure the previous page is still portrait!)

4

41 At the beginning of this new page do the following steps:

 Insert a SmartArt of Basic Cycle style,

4

Page 7 of 7

 Enter the text as 1, 2, 3, 4, and 5 for each of the SmartArt process circles,

 Change the SmarArt Colors to Colorful – Accent Colors type, and

 Change the SmartArt Styles to Polished.

Note: The color of the Smart Art in our sample output may differ slightly from the color in your output.

42

Move your cursor to the end of the document and press Enter key TWICE. Insert an Online Picture of a Computer,

 Size the Online Picture to approximately 2” height and 2” width, and

 Set the Online Picture Picture Style to Metal Oval (last style in the gallery!).

4

43

Move your cursor to the end of the document and press Enter key TWICE and do the following steps:

 At this cursor position, Insert a WordArt of any style, and

 Enter WORD PROJECT for the WordArt text.

 Set the font size of the Word Art to 36.

3

44 Move your cursor to the blank line after the SmartArt object and insert Two Columns with a Line between (make

sure to select the option Apply to: This point forward). 2

45

Move your cursor to the beginning of the document and do the following steps:

 Insert a Cover Page, choose Grid Style,

 Enter WORD PROJECT as the document title,

 Enter Your Name as the document subtitle

4

46 Save Lastname_Firstname_18PR_Word.docx file and Close Word application. 0

47 Save Lastname_Firstname_18PR_Excel.xlsx and Close Excel application. 0

48

Make sure all files for the above Hands-On exists in the Lastname_Firstname_PROJECT folder. The files include:

1) Lastname_Firstname_18PR_PowerPoint.pptx (from previous part 1 of project)

2) Lastname_Firstname_18PR_Access.accdb,

3) Lastname_Firstname_18PR_Excel.xlsx,

4) Lastname_Firstname_18PR_Word.docx,

5) File4Import.xlsx, and

6) Logo2Copy.wmf

0

49

 Zip the Lastname_Firstname_PROJECT folder and submit your zipped project folder (Lastname_Firstname_PROJECT.zip) using the (18PR) Project UPLOAD link.

Note: If you need to review how to zip a file into Eagle Online review the (01FM) module.

0

TOTAL POINTS 100

(18PR) PPT Project Instructions.pdf