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

GETTING STARTED

1. Save the file SC_AC365_9A_FirstLastName_1.accdb as SC_AC365_9A_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_9A_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 working with the company database, you are performing basic database administration tasks to make sure everyone can use the database efficiently. Users often ask when the database started tracking sales contacts. Create a custom database property to record this information as follows:

a. From the Info screen in Backstage View, open the Properties dialog box for the database. (Hint: Click the View and edit database properties link.)

b. Add a custom property with Contact as the property name.

c. Select Date as the type.

d. Enter 09/10/2029 as the value. Confirm that your custom property matches the one shown in Figure 1, and then click OK. (Hint: Your database may contain other properties.)

Figure 1: Custom Tab in the Properties Dialog Box

2. Users prefer to select forms and reports from a Navigation form rather than the Navigation Pane. Set an option as follows to display a Navigation form when the database opens:

a. Open the Access Options dialog box.

b. Change the Display Form option in the Current Database category to display the Main Menu Navigation form as shown in Figure 2.

c. When a Microsoft Access dialog box indicates you must close and reopen the current database for the specified option to take effect, click OK.

Figure 2: Access Options Dialog Box

3. Open the Services table in Design View to change a property as follows to help users enter data accurately:

a. Create a custom input mask for the ServiceID field so that each field value consists of two uppercase letters and four numbers. (Hint: Use LL9999 as the input mask.)

b. Save the changes to the table and then close it.

4. Open the ProgramContacts table in Design View to modify the Address multiple-field index as follows:

a. Open the Indexes dialog box.

b. Change the sort order for the PostalCode field to Descending. Save the changes to the ProgramContacts table and then close it.

5. Open the Contacts table in Design View, and then create a single-field index on the LastName field to speed up record sorting. The index should allow duplicate values. Save the changes to the table design but do not close the table.

6. With the Contacts table still open in Design View, create a multiple-field index as follows to improve record retrieval:

a. Use Location as the name of the index.

b. Use the State field, sorted in Ascending order, as the first field in the index.

c. Use the City field, sorted in Ascending order, as the second field in the index. Save the changes to the Contacts table and then close it.

7. Open the Accounts table in Design View to improve data-entry accuracy as follows:

a. Open the Property Sheet for the table.

b. Create a validation rule requiring that the DownPmt field value is always equal to the MonthlyBill field value. (Hint: Use [DownPmt]=[MonthlyBill] as the validation rule.)

c. Use Down payment must be the same as the monthly bill as the validation text. Close the Property Sheet, and then save the changes to the table. (Hint: When asked if you want to test the existing data, click No.)

8. With the Accounts table still open in Design View, create a multiple-field index as follows:

a. Use AccountInfo as the name of the index.

b. Use the CompanyID field, sorted in Ascending order, as the first field in the index.

c. Use the AccountNumber field, sorted in Ascending order, as the second field in the index. Save the changes to the Accounts table, and then close it.

9. Create a blank form based on the 1 Right application part. (Hint: The blank form will appear in the Navigation Pane as a form object named SingleOneColumnRightLabels and is saved automatically.)

10. Use the Table Analyzer to analyze the Accounts table for redundancy as follows:

a. Select the Accounts table as the table to analyze and let the wizard decide what fields go in each table.

b. Rename Table 1 as AltAccounts using the Rename table button.

c. Rename Table 2 as AltServices as shown in Figure 3.

d. Accept the AccountNumber field in the AltAccounts table and the Generated Unique ID field in the AltServices table to uniquely identify records.

e. Delete any values in the Correction column to prevent changing values in the ServiceID field of the AltServices table.

f. Do not create a query that resembles the original table.

g. When a message indicates the command or action TileHorizontally isn't available now, click OK, and then close the AltAccounts and AltServices tables. Confirm the Tables category in the Navigation Pane matches Figure 4.

Figure 3: Table Analyzer Wizard

Figure 4: Navigation Pane with New Tables

11. Customize the Navigation Pane as follows to focus on objects associated with the Contacts table:

a. Switch to viewing database items in the Navigation Pane by the Contacts category.

b. Add the ContactsAndPrograms and ContactSearch forms to the Contacts category.

12. Add a new group to the Contacts category as follows to list reports associated with the Contacts table:

a. Use Contact Reports as the name for the new group.

b. If necessary, move the Contact Reports group so that it appears between the Contact Forms and the Unassigned Objects groups.

c. In the Navigation Pane, add the ContactAddresses report to the new group. Confirm that the Navigation Pane matches Figure 5.

Figure 5: Contacts Category in the Navigation Pane

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.