Reference no: EM132475511

Assignment 1:

Instructions

Create a Database

1. Start Access and create a Blank database, naming it "Inventory."

2. On the Home tab, click View and then name the new table "Inventory."

3. In Design view, change the field name ID to Item ID and verify that it's data type AutoNumber and has been selected as the primary key.

4. In Design view, add the remaining fields and corresponding data types:

5. Save and then close the Inventory table.

6. Create a second table in Design view. Name the table "Merchants."

7. Add the following fields and corresponding data types. Be sure Merchant ID is the primary key:

8. Save and then close the Merchants table.

9. Create a relationship by linking the Merchant ID in the Inventory table to the Merchant ID in the Merchants table. Be sure to enforce referential integrity.

Create Forms and Populate the Database

1. Create a Merchants form that looks similar to the figure below.

2. Create an Inventory form that looks similar to the figure below.

3. Use the Merchants form to populate the appropriate sections in the Merchants table with the records listed below.
4. Use the Inventory form to populate the appropriate sections in the Inventory table with the records listed below.

Query the Database

1. Create a select query that retrieves the Item Name, Purchase Date, Purchase Price, and Online Purchase fields from the Inventory table for items purchased online.
a. Have the select query sort the results in chronological order by purchase date.
b. Be sure to format the Datasheet view so that all field names and data are displayed entirely.
c. Save the query, naming it "Online Purchases," and then close the query.

2. Create a select query that retrieves the Merchant Name from the Merchants table and the Item Name, Category, Manufacturer, and Purchase Date from the Inventory table for appliances.
a. Have the select query sort the results in alphabetical order by merchant name.
b. Be sure to format the Datasheet view so that all field names and data are displayed entirely.
c. Save the query, naming it "Appliance Purchases," and then close the query.

Create Reports

1. Create a tabular report using the Online Purchases query.
a. Sort the report data by purchase date.
b. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page, as shown in the figure.

c. Save the report, naming it "Online Purchases," and then close the report.

2. Create a tabular report using the Appliance Purchases query.
a. Reduce field widths so that all fields are displayed in portrait orientation.
b. Move the Page 1 of 1 footer so that it's centered below the report data.
c. Delete the record count and the summary line below the Merchant Name data.
d. Group the report data by merchant name.
e. Sort the report data by purchase date.
f. Print Preview your report and adjust formatting as necessary, making sure all data are displayed on one page, as shown in the figure.

g. Save the report, naming it "Appliance Purchases," and then close the report.

Assignment 2:

Retailers Database

Follow the steps below to create the Retailers database.
Click to download the files you'll need to complete this graded project.

1. Start Access and create a new blank database named "Retailers."

2. Name the new table "Retailers" and then display it in Design view.

3. Edit the ID field to be "Retailer ID" and verify that it's data type AutoNumber and that it has been selected as the primary key.

4. In Design view, add the remaining fields and corresponding data types:

5. Save and then close the Retailers table.

6. Create a new table named "Sales Reps" and then display it in Design view.

7. Edit the ID field to be Sales Rep ID and verify that it's data type AutoNumber and that it has been selected as the primary key.

8. Add a field named "Retailer ID" and then select the Lookup Wizard as the Data Type.
a. Use the Wizard to select Retailers as the table for the Lookup field.
b. Click Retailer ID as the field with the values to choose from.
c. Sort the values in Ascending order.
d. Click Finish.

9. In Design view, add the remaining Sales Reps fields and corresponding data types:
Field Name Data Type
First Name Short Text
Last Name Short Text
Job Title Short Text

10. Save and then close the Sales Reps table.

11. Create a Retailers form that looks similar to the figure below.

12. Create a Sales Reps form that looks similar to the figure.

13. Use the Retailers form to populate the Retailers table with the records listed below.

14. Use the Sales Reps form to populate the Sales Reps table with the records listed below. Note that the Retailer ID is a lookup field and you'll be able to select the value from a list.

15. Format Table Datasheet view for the Retailers and Sales Reps table, adjusting column widths as appropriate.

16. Create a query in Design view that displays complete contact information in alphabetical order by store name. The tables should already display a relationship because of the Lookup field. If not, join the tables appropriately to run the query correctly.

17. Run the query and save it as Contacts.

18. Close the query and any open tables and forms.

19. Close the Retailers database.

Hook Results Spreadsheet
Follow the steps below to create the Hook Results spreadsheet.

1. Start Excel and create a new blank workbook.

2. Enter data as shown in the figure below.
a. Merge and center titles as shown.
b. Format cell titles and labels as bold where indicated.
c. Display borders around all the cells with data.
d. Change the spreadsheet orientation to landscape to accommodate the wide columns on one sheet of paper.

3. In column F, enter formulas that use a function to calculate % Caught with Table (fish caught with Table hook divided by the total number of fish caught).

4. Format the % Caught with Table values as a percent with one decimal place.

5. Insert a Column Chart of type 2-D Clustered Column that charts data in cells A4 through E6.

6. Change the title to Fish Caught by Hook Brand.

7. Change the Chart Style to Style 11, so that your chart looks similar to the figure.

8. Be sure the chart is sized appropriately and positioned under the data.

9. Save the workbook, naming it "Hook Results."

Invitation Letter

Follow the steps below to create the Invitation Letter.

1. Start Word and create a new single-spaced (blank) document.

2. Type "Table Trout Hooks" and then press Enter.

3. Insert an online image of a trout (use "trout" as the search text) and then press Enter.

4. Optional: You can remove the background color of your image, if needed, by following these steps:
a. Select the image.
b. On the Picture Tools Format tab, click Color > Set Transparent Color.
c. Click on the background color of the selected object.

5. Size the image appropriately and then center align the paragraph containing the image.

6. Format the Table Trout Hooks text as Georgia 22 point. Your letter should look similar to the figure.

7. Start the mail merge process.

8. When you select recipients, use an existing list, select the Retailers.accdb database you created earlier, and then select the Contacts query.

9. Type the text shown in the figure below, inserting merge fields and leaving a blank paragraph where indicated. You'll need to Match Fields when you insert the AddressBlock field code so that you can match the Company to the Store Name field.

10. Be sure that you've formatted the indicated text as bold and used your name in the closing. If the email address isn't automatically formatted as a hyperlink, then click Insert > Hyperlink to format it.

11. In the second blank paragraph after ". . . two recent tournaments:" paste a copy of the chart from the Hook Results spreadsheet. Size the chart appropriately, so that it looks similar to the figure.

12. Save the document, naming it "Lodge Lunch."

13. Use the appropriate command to merge the field data and create a document containing the individual letters.

14. Scroll through the letters and be sure that the content requires only one page. If the chart is so big that your letter requires two pages, then close the merged document without saving it and resize the chart in the Lodge Lunch document before performing the merge again. Repeat this process until you produce letters that will print on one page each.

15. Save the merged document, naming it "Merged Lodge Letter."

Event Presentation
Follow the steps below to create the Event Presentation.

1. Start PowerPoint and create a new blank presentation.

2. Change the Theme of the presentation to Wood Type or another appropriate style if you don't have Wood Type.

3. Verify slide 1 is in Title Slide layout and then enter text so that your slide looks similar to the figure below.

4. Add a new slide with a Content with Caption layout (slide 2) and then insert the Table Hook.jpg picture, a data file for this Graded Project. Add text and size the image so that slide 2 looks similar to the figure.

5. Add a new slide with a Title and Content layout (slide 3) and add text as shown in the figure.

6. Add a new slide with a Title and Content layout (slide 4) and add text as shown in the figure below.

7. Add a new slide with a Title and Content layout (slide 5). Edit the title and paste the chart from the Hook Results spreadsheet so that the slide looks similar to the figure below.

8. Add a new slide with a Title and Content layout (slide 6) and add content so that the slide looks similar to the figure. Refer to the Hook Results spreadsheet to get the figure for the first bullet (Hint: The average should be about 35 percent). Insert an online picture of the same trout (if possible) used in the Lodge Letter.

9. Apply the Wind transition to all the slides.

10. Apply the Wipe animation to each of the bullet items on slides 3, 4, and 6.

11. Save the presentation, naming it "Sales Presentation," and then play the slide show from the beginning to check your presentation.

Attachment:- Retailers Database.zip