USA: +1(669)289-8683 
Sign in
Integrated_Projects_Files/i01_PCPT_data1.accdb
ExpertID Firstname Lastname City
WA24-01 Whitney Holland Spokane
WA35-01 Kay Doyle Bellingham
WA51-01 Trevor Smith Seattle
WA71-01 Firstname Lastname Tacoma
CategoryID Merchandise
FSH Fishing
CMP Camping
RC Rock Climbing
WNT Winter Sports
ACT Action Sports
WTR Water Sports
TM Team Sports
RQT Racquet Sports
FNS Fitness
GLF Golf
APL Apparel
FTW Footwear
CategoryID QtrSales ExpertID
TM ¤ 87,022.00 WA24-01
FTW ¤ 43,856.00 WA24-01
CMP ¤ 118,282.00 WA35-01
ACT ¤ 104,493.00 WA35-01
FSH ¤ 45,487.00 WA24-01
APL ¤ 36,082.00 WA24-01
RC ¤ 229,164.00 WA24-01
RC ¤ 152,776.00 WA35-01
GLF ¤ 35,398.00 WA24-01
RQT ¤ 44,085.00 WA24-01
CMP ¤ 177,422.00 WA24-01
WNT ¤ 370,501.00 WA24-01
WNT ¤ 247,001.00 WA35-01
FNS ¤ 418,958.00 WA24-01
RQT ¤ 29,390.00 WA35-01
FSH ¤ 30,325.00 WA35-01
WTR ¤ 160,062.00 WA35-01
TM ¤ 58,015.00 WA35-01
ACT ¤ 156,740.00 WA24-01
WTR ¤ 240,093.00 WA24-01
FNS ¤ 279,305.00 WA35-01
GLF ¤ 23,599.00 WA35-01
APL ¤ 24,055.00 WA35-01
FTW ¤ 29,237.00 WA35-01
FSH ¤ 65,975.00 WA51-01
CMP ¤ 329,845.00 WA51-01
RC ¤ 433,328.00 WA51-01
WNT ¤ 716,002.00 WA51-01
ACT ¤ 288,479.00 WA51-01
WTR ¤ 455,185.00 WA51-01
TM ¤ 149,045.00 WA51-01
RQT ¤ 63,170.00 WA51-01
FNS ¤ 812,916.00 WA51-01
GLF ¤ 45,797.00 WA51-01
APL ¤ 47,164.00 WA51-01
FTW ¤ 62,712.00 WA51-01
FSH ¤ 40,162.00 WA71-01
CMP ¤ 84,141.00 WA71-01
RC ¤ 101,388.00 WA71-01
WNT ¤ 148,500.00 WA71-01
ACT ¤ 77,247.00 WA71-01
WTR ¤ 105,031.00 WA71-01
TM ¤ 54,007.00 WA71-01
RQT ¤ 39,695.00 WA71-01
FNS ¤ 164,653.00 WA71-01
GLF ¤ 36,799.00 WA71-01
APL ¤ 37,027.00 WA71-01
FTW ¤ 39,619.00 WA71-01
ID Firstname Lastname Title Store ID
10-60150 Maria Doralas Sales Associate CO19
10-60177 Jennifer Tham Sales Associate NM40
10-60192 Tamra Alix Sales Manager CO78
10-60195 Byron Hoese Sales Associate WA71
10-60198 Kelley Zeck Sales Associate WA71
10-60251 Nello Mallory Sales Associate CO20
10-60258 Juan Cerdaro Sales Associate NM73
10-60262 Frieda Ginsburg Sales Associate CO19
10-60285 Margaret Fitzpatrick Sales Associate OR86
10-60299 Chet Lee Sales Associate NM88
10-60359 Reva Lanter Sales Associate NM40
10-60375 Elena Montoya Sales Associate CO19
10-60392 Marcus Simmons Sales Associate OR86
10-60429 Salvador Shames Sales Associate OR86
10-60438 Julette Villanyeve Sales Associate NM40
10-60492 Ellen Gula Sales Associate OR86
10-60722 Vicki Plasek Sales Associate NM52
10-60736 Brendon Gallager Sales Manager OR86
10-60739 Mara Monell Sales Associate WA35
10-60781 Dave Demoura Sales Associate NM88
10-60782 Karen Jones Sales Associate NM88
10-60792 Casey Drees Sales Associate NM88
10-60822 Vic Fowler Sales Associate OR10
10-60825 Naomi Deford Sales Associate OR10
10-60826 Jim Wolfgang Sales Manager NM88
10-60911 Adrea Rhyn Sales Associate OR40
10-60924 Emily Rhoades Sales Associate CO78
10-60925 Jerry Chung Sales Associate OR40
10-60926 Johnie Borello Sales Associate NM88
10-60927 Jake Kettler Sales Associate OR10
10-60944 Sergio Bergan Sales Associate CO19
10-60966 Issac Boerner Sales Associate OR10
10-60971 Michael Stober Sales Associate OR10
10-60977 Skylar Weiden Sales Associate NM40
10-60985 Clifton Durfey Sales Associate WA35
10-60988 Margaret Voboril Sales Manager NM52
10-70115 Heidi Hermann Sales Associate NM40
10-70155 Sandy Elry Sales Associate WA35
10-70175 Jamie Maciejewski Sales Associate NM73
10-70176 Jeff Paolino Sales Associate WA35
10-70193 Casey Creeden Sales Associate NM40
10-70259 Alejandra Balentine Sales Associate NM73
10-70278 Angelique Edstrom Sales Associate WA35
10-70352 Anita Delgado Sales Associate CO19
10-70495 Maricela Fisler Sales Associate NM73
10-70529 Fernando Lopez Sales Associate OR40
10-70578 Helga Cowens Sales Associate CO20
10-70585 Rhea Hopkins Sales Associate NM88
10-70588 Stuart Burbage Sales Associate NM88
10-70625 Lesa Ficke Sales Associate OR40
10-70629 Doretha Glosson Sales Associate CO20
10-70699 Denice Faeth Sales Associate OR69
10-70762 Rae Gerhard Sales Associate NM88
10-70825 Marquite Amodeo Sales Associate OR69
10-70845 Judi Crowston Sales Associate OR40
10-70877 Austin Hallman Sales Associate OR40
10-70915 Araceli Saine Sales Associate OR69
10-70927 Stuart Brittan Sales Associate CO20
10-70955 Fredrick Clune Sales Manager WA35
10-70966 Olivia Perez Sales Associate NM73
SELECT DISTINCTROW *
FROM Merchandise;
PARAMETERS __CategoryID Value;
SELECT DISTINCTROW *
FROM [Quarterly Sales] AS [Merchandise Sales Entry Form]
WHERE ([__CategoryID] = CategoryID);
SELECT [Category Experts].Firstname, [Category Experts].Lastname, [Category Experts].City, [Quarterly Sales].QtrSales, Merchandise.Merchandise
FROM Merchandise INNER JOIN ([Category Experts] INNER JOIN [Quarterly Sales] ON [Category Experts].ExpertID=[Quarterly Sales].ExpertID) ON Merchandise.CategoryID=[Quarterly Sales].CategoryID
ORDER BY [Category Experts].Firstname, [Category Experts].Lastname, Merchandise.Merchandise;
SELECT DISTINCTROW *
FROM [Category Experts];
PARAMETERS __ExpertID Value;
SELECT DISTINCTROW *
FROM [Quarterly Sales] AS [Category Experts Form]
WHERE ([__ExpertID] = ExpertID);
Integrated_Projects_Files/i01_PCPT_data2 x
Front Range Action Sports
1926 Quebec Street
Denver, CO 80207
(303)555-0970
www.fr-actionsports.com
MEMO TO: Michael Dixon, President
FROM: Frank Osei, Vice President Finance
DATE: November 15, 2012
SUBJECT: Sales for Washington Retail Stores
Our total sales for this quarter represent a 12 percent increase over the previous quarter. The expense of adding merchandise category experts has been cost effective since these new employees have contributed greatly to this success. Stores in Washington increased sales more than stores in the other states. Below is the breakdown by merchandise category for our four Washington retail stores:
Merchandise
Spokane
Bellingham
Seattle
Tacoma
Fishing
$45,487
$30,325
$65,975
$40,162
Camping
$177,422
$118,282
$329,845
$84,141
Rock Climbing
$229,164
$152,776
$433,328
$101,388
Winter Sports
$370,501
$247,001
$716,002
$148,500
Action Sports
$156,740
$104,493
$288,479
$77,247
Water Sports
$240,093
$160,062
$455,185
$105,031
Team Sports
$87,022
$58,015
$149,045
$54,007
Racquet Sports
$44,085
$29,390
$63,170
$39,695
Fitness
$418,958
$279,305
$812,916
$164,653
Golf
$35,398
$23,599
$45,797
$36,799
Apparel
$36,082
$24,055
$47,164
$37,027
Footwear
$43,856
$29,237
$62,712
$39,619
Integrated_Projects_Files/i01_PCPT_data3.pptx
Front Range
Action Sports
Washington Retail Stores
1
Our category EXPERTS
Spokane
Whitney Holland
Bellingham
Kay Doyle
Seattle
Trevor Smith
Tacoma
Firstname Lastname
2
Contact Us
1926 Quebec St.
Denver, CO 80207
(303)555-0970
www.fr-actionsports.com
3
Integrated_Projects_Files/Integrated Projects Homework Instructions x
Office 2010 – Homework Assignment– Instructions GO! Integrated Projects Chapter 1
Integrated Projects Chapter 1
Homework Assignment
Project Description:
In this project, you will integrate Word, Excel, Access and PowerPoint. Note that this assignment is not a MyITLab Grader Project. Instead, after completing the steps you will upload your completed documents to a Dropbox. Your instructor will be manually grading this assignment.
Instructions:
For the purpose of grading of the project you are required to perform the following tasks:
Step
Instructions
Points Possible
1
Open the Access database i01_PCPT_data1. Save the database as Lastname_Firstname_Washington_Database replacing Lastname_Firstname with your name. Make sure to select a location you can easily find it such as a folder on your USB or a folder on the Desktop of your computer. All files for this project should be saved in this same folder. If needed, Enable Content.
1
2
Open the table Category Experts. Edit the last record replacing Firstname Lastname with your name. Close the table.
1
3
Open the query Qtrly Sales by Category Expert. Verify your name displays. Note that the query is sorted by First Name so depending on the spelling of your first name your records may not be listed first.
1
4
Export the query to Excel. Browse and save it in the same folder you saved your database file as Lastname_Firstname_Washington_Sales replacing Lastname_Firstname with your name. Verify the file format is Excel Workbook (*.xlsx). Select the Export data with formatting and layout check box. Do not save the export steps. Close the query.
5
5
Open your exported Excel workbook Lastname_Firstname_Washington_Sales. Create a table with headers. Sort the Merchandise column in ascending order. Add a Total Row. Set the Merchandise total currently displayed to None. Sum the Quarterly Sales column. Save the edited workbook and leave it open.
5
6
Open the Word document i01_PCPT_data2 Save the document in the same folder as your other project files as Lastname_Firstname_Washington_Memo replacing Lastname_Firstname with your name. In the document footer, insert the FileName field.
3
7
Select the table. Apply the Table Style Light List – Accent 1. Use the AutoFit Contents option, and center the entire table horizontally. Copy the selected table to the Clipboard.
2
8
In your Excel workbook, insert a new worksheet. Rename the sheet Sales by Store Select A3 and paste the Word table.
2
9
For column A, set the Column Width to 18. For columns B:F, set Column Width to 14. For rows 4:15, use AutoFit Row Height. Select the range A3:E15, and set No Border.
2
10
In F3 type Total Sales In F4 insert the Sum function to total the sales for Fishing merchandise. Use the fill handle to copy the Sum function through F15. With the range F4:F15 still selected, increase the font size to 12.
4
11
In A16 type Totals and center this text. In B16 insert the Sum function to total the sales for the Spokane store. Use the fill handle to copy the Sum function through F16. With B16:F16 still selected, apply the Cell Style under Titles and Headings called Total and also increase the font size to 12.
3
12
In A1 type the title Quarterly Sales for Washington Stores and then merge and center the title over the data. Apply the Cell Style Heading 1.
1
13
Select A3:E15 and insert a 3-D Clustered Column chart. Move the chart to a new sheet named Sales Comparison Add a chart title above the chart called Quarterly Sales for Washington Stores and show the legend at the top.
3
14
On the Sales by Store sheet, select A4:A15 and F4:F15. Insert a 2-D Pie chart. Move the chart to a new sheet named Merchandise Sales and select Chart Layout 1. Edit the chart title to be Merchandise Sales for Washington Stores
3
15
Select the Sales Comparison and Merchandise Sales sheets. In the footer in the left section, insert the File Name. Now select the Qtrly Sales by Category Expert and the Sales by Store worksheets. In the footer in the left section, insert the File Name.
2
16
Display the Clipboard task pane. On the Merchandise Sales sheet, select the chart and copy it. On the Sales Comparison sheet, select the chart and copy it.
2
17
Display the Word document Lastname_Firstname_Washington_Memo Display the Clipboard task pane. At the end of the Word document, paste the Excel column chart. Save the Word document and leave it open. Save the Excel workbook and leave it open.
2
18
Open the PowerPoint presentation i01_PCPT_data3 Save the presentation in the same folder as your other project files as Lastname_Firstname_Washington_Slides replacing Lastname_Firstname with your name. In the footer for Notes and Handouts, check the Date and time Update automatically, Page number and Footer and type the footer text Lastname_Firstname_Washington_Slides and apply it to all slides.
2
19
Display Slide 2, insert two new blank slides. Slides 3 and 4: hide the background graphics. Display the Clipboard task pane. On Slide 3, paste the column chart. On Slide 4, paste the pie chart.
3
20
On Slide 2, replace Firstname Lastname with your name. Convert this placeholder to SmartArt selecting Vertical Block List from the gallery. Apply the animation Split. Select the entire SmartArt graphic, and copy it to the Clipboard task pane.
3
21
Save the PowerPoint presentation. You will submit it electronically when you have completed this project. Exit PowerPoint.
1
22
In your Excel workbook, display the Sales by Store sheet. Select A18 and paste the SmartArt graphic. Set the width to 6.5 inches.
2
23
On the Qtrly Sales by Category Expert sheet, select A18:D21 and copy the range.
1
24
In the Word document, click the blank line below the table at the bottom of page 1 and press Enter. Type Our fitness merchandise continues to be our top-selling category. These numbers are highlighted below and illustrated in the column chart on the next page. and press Enter.
1
25
Use Paste Special to paste the Excel range as a linked Microsoft Excel Worksheet Object. Center the pasted object horizontally. Save and close the Word document.
4
26
On the Qtrly Sales by Category Expert sheet, locate the Quarterly Sales amount where the City is Tacoma and the Merchandise is Fitness. Change the amount from 164653 to 200000
On the Sales by Store sheet, select E12. Type 200000
3
27
Save the Excel workbook. You will submit it electronically when you have completed this project. Exit Excel.
2
28
Open the Word document Lastname_Firstname_Washington_Memo and update the data from the linked file. At the bottom of page 1, verify the Tacoma sales updated to $200,000. On page 2, notice the chart did not update to reflect the $200,000 amount for Tacoma Fitness sales because the chart was pasted without creating a link to the Excel file. In the table on the middle of page 1, edit the Fitness amount for Tacoma to $200,000
4
29
Display your Access database. Select the Merchandise table. Export the table to Word as Rich Text (RTF File). Browse and save it in the same folder as your other project files as Lastname_Firstname_Rich_Text replacing Lastname_Firstname with your name. Do not open the destination file after the export, and do not save the export steps. Exit Access.
5
30
At the end of the Word document, press Enter. Type Below is an alphabetical list of the new IDs assigned to our merchandise categories: and press Enter.
1
31
Insert the Rich Text file Lastname_Firstname_Rich_Text Apply the Table Style Light List – Accent 1. Use the AutoFit Contents option, and center the table horizontally. Save the document.
5
32
Use Mail Merge to select recipients from Lastname_Firstname_Washington_Database database and the Sales Associates table. Edit the recipient list. Filter the Store ID field to equal WA71 and display just the two sales associates for Bellingham, Washington—Byron Hoese and Kelley Zeck.
5
33
At the top of the Word document in the MEMO TO: line, delete the text Michael Dixon, President. Insert the Merge fields for <