Shelly Cashman Access 365 | Module 9: SAM Critical Thinking Project C Connect Marketing Group
Administering A Database System
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_CT9C_FirstLastName_1.accdb as SC_AC365_CT9C_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_CT9C_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. Open the Properties dialog box for the database.
b. Add a custom property with Contact as the property name.
c. Enter 09/10/2029 as the value.
d. Select the appropriate type for the value. Confirm that your custom property matches the one shown in Figure 1, and then accept the custom property changes.
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. Set an Access option to display the Main Menu Navigation form when the current database opens as shown in Figure 2.
b. 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. Change a property in the Services table 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 required uppercase letters and four optional numbers.
b. Save the changes to the table and then close it.
4. Modify the Address multiple-field index in the ProgramContacts table to change a sort order 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. Create a single-field index on the LastName field in the Contacts table 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. In the Contacts table, create a multiple-field index as follows to improve record retrieval:
a. Use Location as the name of the index.
b. Sort the Location index first by the State field in Ascending order.
c. Sort the index next by the City field in Ascending order. Save the changes to the Contacts table and then close it.
7. Improve data-entry accuracy in the Accounts table 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.
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 without testing the existing data.
8. Create a multiple-field index in the Accounts table as follows:
a. Use AccountInfo as the name of the index.
b. Sort the AccountInfo index first by the CompanyID field in Ascending order.
c. Sort the index next by the AccountNumber field in Ascending order. Save the changes to the Accounts table, and then close it.
9. Create a blank form based on the 1 Right application part using the default name for the form.
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. Use AltAccounts as the name of Table 1.
c. Use AltServices as the name of Table 2 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 Contact Forms group.
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.