CIS 105 Maricopa Community College Macros & SQL Statement Microsoft Access Lesson 12: Project Directions Hands-On Project View Glossary Overview In this h

CIS 105 Maricopa Community College Macros & SQL Statement Microsoft Access Lesson 12: Project Directions
Hands-On Project
View Glossary
Overview

In this hands-on project, you will be downloading a database from the Lifetime Media Company, a film and graphics company that does onsite filming and photos. The company uses this database to manage equipment assigned to its employees. You will be completing the following tasks in Microsoft Access:

Creating a stand-alone macro
Creating an embedded macro.
Creating an event-driven data macro
Creating a query by writing an SQL statement
Project Preparation
Download the Lesson 12_Hands On Project file to your course lesson folder.
Extract the file to the same folder.
Open the file in Access.
Save your file as “Lesson12_Project1_MEID.accdb” where MEID is your MEID.
Project Directions
Open the Macro Designer and create a stand-alone macro to automatically open the Lifetime Media Equipment table.
Select the following arguments:
Table Name: Lifetime Media Equipment
View: Datasheet (default)
Date Mode: Edit (default)
Save the Macro as ‘Open Name Objects’ where name is your first name (example: Open John’s Objects)
Right-click the ‘Open Name Objects’ macro in the database and select Design View.
Add an OpenForm action and the following arguments:
Form Name: Equipment Checkout Form
View: Form(default)
Window Mode: Normal (default)
Save and close the Macro.
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.
Select ‘On Close’ from the Event list.
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.
In the Macro Builder, choose Message Box from the Actions drop-down menu.
In the Message box type: ‘Send Report to Auditor each Friday’.
Beep: Yes
Type: Information
Title: ‘Reminder’
Save and close the macro.
You can test your macro by opening the Equipment Assignments report from the Navigation pane and then closing the report.
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.
Add a field titled ‘ReplacementRequired’ and format the field as Short Text.
Add a field titled ‘ReplacementAmountPerItem’ and format the field as Currency. Save the Table before moving on to the next step.
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.

Event-Driven Data Macro – Sample Figure

If the ‘ReplacementRequired’ field = “YES” you will SetField ‘ReplacementAmountPerItem’ to be equal to 60% of the Per Unit Cost amount.
Save and Close the macro.
In the Lifetime Media Equipment table enter “YES” for ‘ReplacementRequired’ for records 2, 8, 9, 24, and 25. Enter “NO” for the rest.
In the Lifetime Media Equipment table, add the following 2 records:
One DSLR HD Camera for Jones at a per unit cost of $875.00. Replacement will not be required.
One Studio HD Camera for Bernard at a per unit cost of $1200.00. Replacement will be required.
Note: The replacement amounts are automatically calculated.
Save and Close the Lifetime Media Equipment Table.
Open a new query in SQL View.
Type the following commands:
SELECT [Employee Assigned To], [Item Name], [Quantity]
FROM [Lifetime Media Equipment]
WHERE [Per Unit Cost] >700
;
Run the query.
View your results in Datasheet View.
Save the query with the title Items over $700
Close the Query
Compact, repair, and save your database.
Submit the assignment based on the instructions provided in the lesson.