At-Home Final Exam Project 1

Microsoft Access

You have done quite a bit of work so far researching computers in order to make the best possible computer purchase. You feel very confident based on the PowerPoint presentation you have created, and you have compiled and formatted quite a bit of data to analyze using Microsoft Excel. At some point, you may want to share this information with bigger audiences or add even more data so you have decided to move your Excel information into Microsoft Access.  

Instructions

Import the Data

1. Open your ComputerChoices_MEID.xlsm file from Part 3 (Excel) and do the following:
   • Save the file with a new file name “ComputerChoices-Import.xlsx.” (Note: This is NOT a macro-enabled workbook.)
   • Delete Row 1(with your Purchasing a Computer - Data title). (HINT: At this point you should have a simple data table with headings at the top of each column).
   • Delete the rows containing the total and average values.
   • Remove the conditional formatting from Column F.
   • Delete the Analysis, Brands, and Focus worksheets. 
   • Keep the Data worksheet only. 
   • Save the file.
2. Create a new blank Access database.
3. Save the Access database as “ComputerChoices_MEID.accdb.” Note: Replace MEID with your MEID number.
4. Import the ComputerChoices-Import.xlsx Excel spreadsheet as external data.
5. Be sure to check the First Row Contains Column Headings. Let Access add the Primary Key. Name the new table Friend Data. Once you finish your import, open the Friend Data table to verify your data has imported correctly and is sorted in ascending order by Name.
6. Make the following field property changes:  Name: Change the field size to 50. Note: Increase the field size if needed to match the longest name you created and entered.
7. Repeat the above process for Computer Brand.
8. Price: Change the caption to Computer Purchase Price.  
9. Save the design changes.
10. Choose yes if you receive the "Some data may be lost" warning.

Create a Form

1. Create a new form with a title of Computer Information and input fields for each of your six data points.
   • Adjust the data field sizes so they all fit to the page.
   • Apply a theme of your choice to the form for visual interest.
   • Save the form as Computer Information.
2. Go back into Design Mode for the Form
   • Remove the Computer Brand field.
   • Create a new Drop Down object where you will type in the choices for the Brands. Bind the drop down to the Computer Brand field. (HINT: You should type in all of the brands that exist in your data).
3. Verify your new Computer Information form works by entering a new acquaintance record using your instructor's last name as the name. You may choose the data for the other fields.

Create a Query

1. Create a simple query from the Friend Data Table including the Name, Computer Brand, Price, and Length of Ownership. Title the query Pricing.
   • Sort by Price in Descending Order.
   • Create a new expression within the Query called "Durability" that divides the Price by the Length of Ownership. Format this new field as Currency.
   • Run the query to ensure that the calculated field was created correctly.
   • Close and Save the Query.

Create a Report

1. Create a new report using your Pricing query. (HINT: Use the Report Wizard)
   • Include all fields from the query on the report.
   • Group the report by Computer Brand.
   • Sort the report by Name.
   • Change the report title to Pricing by Acquaintance and ensure your fields fit within the report page borders.
   • Apply a theme of your choice to the report and save the report.
   • Create an embedded macro within the Report that displays a reminder box each time it is closed, the box should display "Be sure to choose wisely!"
   • Close and save the report.
   • Reopen the report and then close it again to ensure that your macro runs and displays the message box as intended.

Submit your Project 

1. Close all open objects, making sure to save any that have not yet been saved. Close the database.
2. Zip all project files together into a single zipped folder. There should be 6 files:
    
   • Hardware_MEID.docx
   • Storyboard_MEID.docx
   • ComputerPurchase_MEID.pptx
   • ComputerChoices_MEID.xlsm
   • ComputerChoices-Import.xlsx
   • ComputerChoices_MEID.accdb