Shelly Cashman Access 365 | Module 3: SAM Project A Connect Marketing Group

GETTING STARTED

1. Save the file SC_AC365_3A_FirstLastName_1.accdb as SC_AC365_3A_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_3A_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. Do not close the form.

2. Switch the CompaniesSplit form to Form View, and then add a record using the values shown in Table 1 to enter data for a new company. Close the CompaniesSplit form.

Table 1: Values for the New Record

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

3. Open the ContactsSplit form in Form View to maintain records for Connect Marketing contacts. Update contact records as follows:

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

b. In the Address field, 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. Open the Services table in Design View and add a lookup field as follows to make it easy to enter service types:

a. Use the Lookup Wizard to change the ServiceType field to a Lookup field. Select the option to type in the values that you want.

b. Enter the values shown 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, but do not close the table.

Table 2: Values for the ServiceType Lookup Field

E-commerce Marketing Retail Sales

5. Switch the Services table to Datasheet View and then correct an error by using the lookup list to change the ServiceType field value to Marketing for ServiceID SV1504. Close the Services table.

6. Open the Accounts table in Datasheet View, and then add a field to the table 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 the Currency format.

b. Use the expression [MonthlyBill]*12 to calculate the field values.

c. Assign the name AnnualBill to the field.

d. Save the changes to the Accounts table and then 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. In Query Design View, 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 those records where the ServiceType field values equal Sales.

d. Update the Team field using [Team]+2 as the expression for the new 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. In Query Design View, 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. Select all the fields individually for the design grid; do not use the (*) asterisk.

b. Add criteria to select only those records where the State field value is FL or GA.

c. In the Make Table dialog box, 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. In Query Design View, create a Delete query for the Accounts table.

b. Select the MonthlyBill field from the Accounts table.

c. Add criteria to delete only those records where the MonthlyBill field values equal 0.

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. In Query Design View, 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. Select all the fields individually for the design grid; do not use the (*) asterisk.

c. Add the criteria "OR" to the State field.

d. Select WestCoast as the destination table in the Append dialog box.

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

11. Open the Companies table in Design View and then 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. Open the Services table in Design View to add field properties that 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. Enter the text, Must be less than 15 in the Validation Text property box.

d. Save the changes to the Services table but do not close it. (Hint: Because a validation rule was added, a warning message appears asking if you want to test the data. The data is valid, so ignore this message and continue saving the table.)

13. Switch the Services table to Datasheet View. Because sales reps sometimes show the table to clients, format the datasheet as follows:

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. Open the Accounts table in Datasheet View, and then calculate billing totals as follows:

a. Add a Total row to the datasheet.

b. Calculate the Sum for the MonthlyBill field.

c. Calculate the Average for the ContractTotal field. Save the changes to the table and close it.

15. Open the Relationships window and add the Companies and Accounts tables to the Relationships window. 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. Open the Companies table in Datasheet View and display the Subdatasheet for the company with CompanyID 9 (SuperStar). Update the MonthlyBill amount for account A34354 to $500. 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.