Shelly Cashman Access 365 | Module 3: SAM Critical Thinking Project C Connect Marketing Group

GETTING STARTED

1. Save the file SC_AC365_CT3C_FirstLastName_1.accdb as SC_AC365_CT3C_FirstLastName_2.accdb

a. Edit the file name by changing “1” to “2”.

b. If you do not see the .accdb file extension, do not type it. The file extension will be added for you automatically.

2. With the file SC_AC365_CT3C_FirstLastName_2.accdb open, ensure that your first and last name is displayed as the first record in the _GradingInfoTable table.

a. If the table does not display your name, delete the file and download a new copy.

PROJECT STEPS

1. Connect Marketing Group is a national company that provides marketing services such as contact research, brand building, and merchandising to retail stores. As a sales analyst, you need to update the company database regularly. You want to improve the table designs and use forms to enter and maintain data. Create a Split Form for the Companies table and save it, using CompaniesSplit as the name.

2. Add a record to the CompaniesSplit form using the values shown in Table 1 to enter data for a new company, and then close the form.

Table 1: Values for the New Record

CompanyID | [AutoNumber field—press TAB] CompanyName | Home Market RetailStores | 35

3. Use the ContactsSplit form to update records for Connect Marketing contacts as follows:

a. Search for and navigate to the record with the ContactID field value of 20032 for Kareem Aziz.

b. Replace 443 E. Elmwood Ave. with 410 E. Elmwood Ave. as the street address.

c. Search for and navigate to the record with the ContactID field value of 20045 for Ramona Torres, and then delete the record.

d. Close the ContactsSplit form.

4. Connect Marketing provides four types of services to its clients. Add a lookup field to the Services table as follows to make it easy to enter service types:

a. Use the Lookup Wizard to change the ServiceType field to a Lookup field by typing the values you want.

b. Enter the values listed in Table 2 in the order shown.

c. Limit the values to only the items in the list, and do not allow multiple values for the field.

d. Save the changes to the table design.

Table 2: Values for the ServiceType Lookup Field

E-commerce Marketing Retail Sales

5. Correct an error in the Services table data by using the lookup list to change the ServiceType field value to Marketing for ServiceID SV1504. Close the Services table.

6. Add a field to the Accounts table in Datasheet View to calculate the annual payment amount as follows:

a. Add a new field to the end of the Accounts table using the Calculated data type and a Format suitable for dollar amounts.

b. Use an expression that multiplies the values in the MonthlyBill field by 12 to calculate the annual billing amount.

c. Assign the name AnnualBill to the field.

d. Save the changes to the table and close it.

7. Connect Marketing is adding two staff members to every sales team. Update the Services table as follows to reflect this change:

a. Create an Update query based on the Services table.

b. Select the ServiceType and Team fields from the Services table.

c. Add criteria to select only the Sales service type.

d. Update the Team field by using an expression that adds 2 to the current Team field value.

e. Save the query using UpdateServices as the name, run it, and then close it. The query should update four records.

8. Connect Marketing is developing a special program for contacts in Florida and Georgia. Create a new table for contacts in these states as follows:

a. Create a Make Table query that selects all the fields from the Contacts table in the same order that they are listed in the field list.

b. Select only records for contacts who live in Florida or Georgia.

c. Assign the name ProgramContacts to the new table.

d. Save the query using FLGAContacts as the name, run it, and then close it. The new ProgramContacts table should contain seven records.

9. Accounts with a $0.00 monthly billing amount represent completed contracts that can be removed from the Accounts table. Remove the records as follows:

a. Create a Delete query for the Accounts table.

b. Select the MonthlyBill field from the Accounts table.

c. Delete only those records with 0 as the MonthlyBill amount.

d. Save the query using DeleteAccounts as the name, run it, and then close it. The query should delete five records.

10. Contacts in Oregon should be added to the WestCoast table. Add the records as follows:

a. Create an Append query for the Contacts table.

b. Select all the fields from the Contacts table in the same order that they are listed in the field list.

c. Add the criteria "OR" to select all contacts in Oregon.

d. Append the records to the WestCoast table.

e. Save the query using AppendWestCoast as the name, run it, and then close it. The query should append three records.

11. In the Companies table, change the Field Size property for the RetailStores field to Double to save storage space for the field values. Save the changes to the Companies table and then close it.

12. Add field properties to the Services table to improve data-entry accuracy as follows:

a. Add Marketing as the Default Value for the ServiceType field since most services are in the Marketing category.

b. Enter a validation rule for the Team field to ensure that values in the field are less than 15 since the maximum team size is 14.

c. Use Must be less than 15 as the validation text to display to users. Save the changes to the Services table but do not close it. (Hint: Although you added a validation rule, the data is still valid.)

13. Format the Services table datasheet as follows to improve its appearance for when sales reps show the table to clients:

a. Display only horizontal gridlines in the datasheet.

b. Change the alternate row color to Gold, Accent 4, Lighter 80% (8th column, 2nd row in the Theme Colors palette). Save the changes to the table and close it.

14. Calculate billing totals on the Accounts table datasheet as follows:

a. Add a Total row to the datasheet.

b. Calculate the sum for of monthly billing amounts.

c. Calculate the average of the contract totals. Save the changes to the table and close it.

15. Create a one-to-many relationship between the CompanyID field in the Companies table and the CompanyID field in the Accounts table. Make the relationship enforce referential integrity. Save the relationship.

16. Display the subdatasheet for the SuperStar company in the Companies table datasheet. Use $500 as the MonthlyBill amount for account A34354. Close the Companies table.

Save and close any open objects in your database. Compact and repair your database, close it, and then exit Access. Follow the directions on the website to submit your completed project.