Information Systems

MICROSOFT ACCESS PROJECT

 ** MUST HAVE MICROSOFT ACCESS PROGRAM TO COMPLETE **

** MUST COMPLETE ALL STEPS AND THIS WILL BE DOUBLE CHECKED **

** SECOND IMAGE ATTACHED TO ASSIGNMENT IS WHAT FINISHED PRODUCT SHOULD LOOK LIKE **

  1. Download the Lesson 12 project file (ATTACHED) to a folder titled Lesson 12
  2. Extract the file to the same folder.
  3. Open the file in Access.
  4. Save your file as “Lesson12_Project1_MEID.accdb” where MEID is your MEID.

Project Directions

  1. Open the Macro Designer and create a stand-alone macro to automatically open the Lifetime Media Equipment table. 
  2. Select the following arguments:
    1. Table Name: Lifetime Media Equipment
    2. View: Datasheet (default)
    3. Date Mode: Edit (default)
  3. Save the Macro as ‘Open Name Objects’ where name is your first name (example: Open John’s Objects)
  4. Right-click the ‘Open Name Objects’ macro in the database and select Design View. 
  5. Add an OpenForm action and the following arguments:
    1. Form Name: Equipment Checkout Form
    2. View: Form(default)
    3. Window Mode: Normal (default)
  6. Save and close the Macro. 
  7. Right-click on the Equipment Assignments Report and open in Design View. The Property Sheet should also be on the right of your screen.  You will now create a macro to send a reminder to the user upon closing the report to send an email to the auditor each Friday. 
  8. Select ‘On Close’ from the Event list. 
  9. Use the Macro Builder to create an embed macro that will prompt the user to send an Equipment Assignment report to the auditor each Friday by displaying a Message Box. 
    1. In the Macro Builder, choose Message Box from the Actions drop-down menu.
    2. In the Message box type: ‘Send Report to Auditor each Friday’.
    3. Beep: Yes
    4. Type: Information
    5. Title: ‘Reminder’
  10. Save and close the macro. 
  11. You can test your macro by opening the Equipment Assignments report from the Navigation pane and then closing the report.  
  12. Open the Lifetime Media Equipment table if not already opened. You will be adding fields to indicate if a lost/damaged item needs to be replaced and a field indicating the replacement amount per item.  
  13. Add a field titled ‘ReplacementRequired’ and format the field as Short Text. 
  14. Add a field titled ‘ReplacementAmountPerItem’ and format the field as Currency.
  15. Create an event-driven data macro to calculate a 60% replacement amount when the ‘ReplacementRequired’ field has a value of “YES”.  Enter the arguments per the illustration below. If the ‘ReplacementRequired’ field has a value of “NO” the ‘ReplacementAmountPerItem’ field will remain empty. 
  16. If the ‘ReplacementRequired’ field = “YES” you will SetField ‘ReplacementAmountPerItem’ to be equal to 60% of the Per Unit Cost amount.  
  17. Save and Close the macro. 
  18. In the Lifetime Media Equipment table enter “YES” for ‘ReplacementRequired’ for records 2, 8, 9, 24, and 25. Enter “NO” for the rest.  
  19. In the Lifetime Media Equipment table, add the following 2 records:
    1. One DSLR HD Camera for Jones at a per unit cost of $875.00. Replacement will not be required.
    2. One Studio HD Camera for Bernard at a per unit cost of $1200.00. Replacement will be required.
  20. Note: The replacement amounts are automatically calculated. 
  21. Save and Close the Lifetime Media Equipment Table. 
  22. Open a new query in SQL View. 
  23. Type the following commands:
    1. SELECT [Employee Assigned To], [Item Name], [Quantity]
    2. FROM [Lifetime Media Equipment]
    3. WHERE [Per Unit Cost] >700
    4. ;
  24. Run the query.
  25. View your results in Datasheet View.
  26. Save the query with the title Items over $700 
  27. Close the Query
  28. Compact, repair, and save your database. 
  29. Submit the assignment based on the instructions provided in the lesson.