excel access and database

Part 3: Excel Spreadsheet

Now that you have learned how useful Excel can be, you would like to apply some needed organization to your life. You have long wanted to create a comprehensive list of the titles in your DVD collection so that you see which movies you own at a glance. You will use a new, blank Excel spreadsheet to complete the following steps:

1. In cell A1, enter the text “My DVD Collection – MM/DD/YY” (using today’s date) for your spreadsheet title.

2. Merge and center cells A1:D1 of your spreadsheet title and format as Arial, 16 pt, bold. Apply the light blue background fill color to the merged title cells.

3. Create the following four columns under your title. Format each column alignment as denoted in the parentheses following the column name (below) and bold the font.

 Title (left)

 Category (left)

 Rating (center)

 Length (right)

4. Apply a thick bottom border under the column headings.

5. Enter a data record for each of your DVDs, completing each column (a minimum of 20 are required for this project). Enter the length of each movie in minutes and use the following categories: comedy, action, horror, drama, romance, and animated to categorize each one. Remember, you must create/enter at least 20 records (DVDs) and have at least one record for each of the six categories.

6. Sort your DVD data alphabetically, in ascending order, by Title.

7. Create and insert a formula to sum the total viewing minutes for your DVD collection at the bottom of the Length column. Apply bold formatting to the total and change the font color to dark blue.

8. Create and insert a pivot table for your DVD data to show a total count of DVDs by category. Filter by the Rating, using “Category” for the Axis Field, and “Count of Title” under the Values menu. You should create the pivot table within the same worksheet as your DVD data.

9. Create and insert a 3D pie chart type from your pivot table to show total DVDs by category. Be sure that you have your pivot table filters for Rating and Category set to select all. Format your pie chart to display the data labels with category name and percentage. You should create the pie chart within the same worksheet as your DVD data and pivot table.

10. Save your Excel file labeled MyDVDCollection_MEID.xlsx.

Part 4: Access Database

You feel a sense of accomplishment at your organizing so far, but at the rate your DVD collection is growing, you realize that moving your data into Access will allow you to easily find select information. Your nieces and nephews frequently visit, and you want to create a report that you can filter by rating so you can quickly produce a list of acceptable movies for children. You also want to create a form so you can easily add to your movie database going forward.

1. Open your MyDVDCollection_MEID.xlsx file from Step 3 (above) and complete the following steps:

 Save the file with the new filename MyDVDCollection-Import.xlsx.

 Delete row 1 (with your spreadsheet title).

 Delete your formula summation that totals the movie length minutes.

 Delete the pivot table.

 Delete the pie chart.

2. Create a new blank Access database.

3. Save the Access database as DVDDatabase_MEID.accdb.

4. Import your Excel spreadsheet from Part 3 as external data. Select Sheet 1 (or the applicable sheet name in your DVD Collection Excel file) and be sure to check “First Row Contains Column Headings.” Let Access assign the Primary Key. Change the name of the table to DVDs. Once you finish your import, open the DVDs table and verify that your data has imported correctly and is sorted in ascending order by title.

5. Make the following field property changes:

 Title: change field size to 50 and caption to DVD Title.

 Category: change field size to 8 and caption to Category.

 Rating: change field size to 5 and caption to Rating.

 Length: change field size to 3, data type to number, and caption to Length.

6. Save your design changes. Select yes at the some data may be lost warning.

7. Create a new Form titled My DVD Collection.

8. Adjust the field sizes so that they all fit on the page.

9. Apply the Wisp theme to the Form.

10. Save the Form as DVD Entry.

11. Verify that your new DVD Entry Form is working by entering a new DVD title with a “G” rating. Include your MEID at the beginning of the new DVD title entry.

12. Create a simple query from the DVDs table including the Title, Category, and Rating fields. Title the query DVD Ratings.

13. Update the Rating filter to include only G and PG movies and sort alphabetically ascending (A-Z).

14. Create a new Report using your DVD Ratings query. Change the Report title to Movies for Children and ensure that your fields fit within the Report page borders. Apply a Theme of your choosing to the report and save the Report as Movies for Children Report.

15. Be sure to save your final Access file as DVDDatabase_MEID.accdb.

 

Do you need a similar assignment done for you from scratch? We have qualified writers to help you. We assure you an A+ quality paper that is free from plagiarism. Order now for an Amazing Discount!
Use Discount Code "Newclient" for a 15% Discount!

NB: We do not resell papers. Upon ordering, we do an original paper exclusively for you.