ACCESS PROBLEM-SOLVING ASSIGNMENT NOTE: This assignment may ask you to perform a task that I have not taught you how to do (and that you may not find in your textbook). This is intentional. The purpose of this assignment is to see how well you problem-solve with the software. You are welcome to research how to do these things using Google or YouTube or whatever other search engines you are comfortable with. For this assignment, you will create an Access database to hold data about your favorite YouTube videos. Getting Started • • • • There are two data files needed for this assignment. They can be found under the appropriate assignment link in Canvas. o Download and save the Excel data file named Customers.xlsx to your computer. o Download and save the Word data file named Letter to Customers.docx to your computer. Open your internet browser to YouTube. Find 15 videos on YouTube that you would be comfortable sharing with the rest of the class (do not use videos that are inappropriate or offensive). Write down the following information about each video: o Name of video o Publisher of video o Length of video o Date video was published o Number of views o Number of likes o Number of dislikes o URL to video Database • Create a blank database. Include your full name in the filename of the database (for example, John Doe DB). Tables • In your new database, create a table to hold your YouTube video data. o The table should be named Videos. o The table should include all of the fields listed above. ▪ Make sure you assign appropriate data types for each field. Do NOT use the data type called “Large Number” because it causes compatibility issues. If you need a number data type, just use the one called “Number.” o • The table should be assigned an appropriate primary key (DO NOT USE AUTONUMBER – come up with your own id #s). o The table should include all of the data you previously wrote down. INTEGRATION EXCEL/ACCESS: Import the excel file named Customers.xlsx into your database as a new table. o The table should be named Customers. o Make sure you assign appropriate data types for each field. Do NOT use the data type called “Large Number” because it causes compatibility issues. If you need a number data type, just use the one called “Number.” o Assign an appropriate primary key (DO NOT USE AUTONUMBER – select a field that already exists in the table). Queries • • Create separate queries to answer each of the following questions. Name the queries using the following structure (Query 1, Query 2, Query 3, & Query 4). Use proper criteria and/or totals for each query. 1. Which customers are paying for their memberships? 2. Which videos were published prior to January 1, 2016? 3. Which videos are longer than 10 minutes? 4. Which video has the most views? Save and close all open objects. Forms • • Create a simple form from the Customers table to allow more efficiency when entering customer data. Save and close all open objects. Reports • • • • • Create a report from the Videos table using the following fields (in this order): Publisher of video, Name of video, Number of likes, Number of dislikes. Group the report by Publisher of video. Sort the report in ascending order by Name of video. Add totals to the report to show the SUMs of both the likes and dislikes. Save and close all open objects. Close Access. Mail Merge INTEGRATION WORD/ACCESS • Open the Letter to Customers.docx file from its saved location. • Click the Mailings tab. • Click Start Mail Merge, and choose Step-by-Step Mail Merge Wizard from the list of options. o In Step 1 of the Mail Merge Wizard (on the right-side of the page) select Letters, and then click Next (at the bottom of the wizard). o In Step 2 of the wizard, select Use Current Document, and then click Next. o In Step 3 of the wizard, select Use an Existing List, and then click Browse (in the middle of the wizard). Navigate to and select the Access database file that you created. When prompted, select the table named Customers as your source and click OK. ▪ At this point you will be given a complete list of every customer in the table. However, we ONLY want to send this letter to the customers who currently have a free account. To do this, scroll over to the Membership Type column (where it lists either Free or Paid). Click the drop-down arrow and click Free. This filters the data so that you are only seeing customers who have free accounts. Click Next. o In Step 4 of the wizard, select the words Dear customer in the greeting line and delete them. In its place, click Greeting Line (from the available merge fields listed in the wizard) to insert the greeting line merge field. Click OK. o In Step 5 of the wizard, click the Next and Previous arrows (at the top of your screen) to see what each letter to each different recipient will look like. You should have 5 letters. Click Next. o In Step 6 of the wizard, just save your document and close it because you are not required to print anything. Submit your completed Access database (that has your full name in the filename) and Word document (named Letter to Customers) to the appropriate assignment link in Canvas. When I try to open your Word document, it will prompt me to connect it to the appropriate source (which is your database), so I will know at that point that you have completed the mail merge correctly. Customer ID C001 C154 C158 C485 C665 C675 C753 Last Name Shakima McNulty Charmin Russell Moreland Omar Preston First Name Greggs James Elizabeth Bell William Little Broudas NICKNAME Date of Birth 3/1/1980 Jimmy 1/1/1999 Lizzy 4/1/1989 5/1/1996 Bill 2/28/2000 Biggy 6/1/1994 7/1/1990 Membership Type FREE FREE FREE PAID FREE PAID FREE Dear customer, On behalf of YouTube, I would like to take this opportunity to thank you for being a customer. It is our hope that you have enjoyed your free account thus far. I wanted to let you know of an exciting opportunity for you to become a YouTube Red member. This paid membership would give you uninterrupted music, ad-free videos, and more! The cost is only $9.99 per month and can be cancelled at any time. If you would like to check it out, we offer a 1-month free trial. Just go to this website https://www.youtube.com/ReD to get started today! Sincerely, Sue Anderson Account Manager
Purchase answer to see full attachment