SC_AC16_9b

Shelly Cashman Access 2016 | Module 9: SAM Project 1b

Shelly Cashman Access 2016 | Module 9: SAM Project 1b

All Around Outside Maintenance

ADMINISTeRING A DATABASE SYSTEM

GETTING STARTED

· Open the file SC_AC16_9b_FirstLastName_1.accdb, available for download from the SAM website.

· Save the file as SC_AC16_9b_FirstLastName_2.accdb by changing the “1” to a “2”.

2. Hint: If you do not see the .accdb file extension in the Save As dialog box, do not type it. Access will add the file extension for you automatically.

· To complete this Project, you will also need to download and save the following support file from the SAM website:

· Support_SC_AC16_9b_PropertyTypes.accdb

· Open the _GradingInfoTable table and ensure that your first and last name is displayed as the first record in the table. If the table does not contain your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

Open the database in Backstage View, and then click the View and edit database properties link. When the Properties dialog box displays, create a custom property for the database as described below:

a. Create a custom property named Service. (Hint: Do not type the period.)

b. Select Date as the type.

c. Enter 02/14/2017 as the value.

Confirm that your custom property matches the one shown in Figure 1 on the next page. (Hint: Your database may contain other properties, depending on the version of Access used to open the file or the method by which the file was downloaded from SAM. Ignore these additional properties.) Save and close the Properties box.

Figure 1: Custom Properties Tab in the Properties Dialog Box

With the database still open in Backstage View, click the Options tab. Select the Main Menu Navigation Form as the display form for the database, as shown in Figure 2 on the next page. (Hint: The display form is sometimes referred to as the startup form.)

Save the changes to the options. (Hint: When the Access dialog box appears and displays the message, “You must close and reopen the current database for the specified option to take effect.”, click the OK button.)

Figure 2: Access Options Dialog Box

Open the Manager table in Design View, and then create a custom input mask for the ManagerID field as described below:

d. The ManagerID field must consist of two letters and two numbers.

e. All letters should display as uppercase. (Hint: Enter >LL99 as the input mask.)

f. Save the changes to the table.

With the Manager table still open in Design View, modify the ManagerName multiple-field index by completing the following tasks:

g. Open the Indexes dialog box.

h. Change the sort order for the LastName field to Descending.

i. Save the changes to the table, and then close it.

Open the Client table in Design View, and then create a single-field index on the ClientName field, using ClientName as the name. (Hint: Be careful not to include spaces after the index name.) The index should allow duplicate values. Save the changes to the table design.

With the Client table still open in Design View, create a multiple-field index as described below:

j. Name the index ClientLocation. (Hint: Be careful not to include spaces after the index name.)

k. Use the State field, sorted in Descending order, as the first field in the index.

l. Use the City field, sorted in Ascending order, as the second field in the index.

m. Save the changes to the table design, and then close the table.

Open the Client Types table in Design View, and then open the Property Sheet for the table and make the following updates:

n. Create a validation rule for the table requiring that the MonthlyFee field value is always less than or equal to the MinRate field value. (Hint: Enter [MonthlyFee]<=[MinRate] as the validation rule.)

o. Enter MonthlyFee cannot exceed MinRate as the validation text.

p. Close the Property Sheet, and then save the changes to the table. (Hint: Because there was a change to data integrity rules, the “existing data may not be valid” warning message will appear. Click Yes and continue saving the table. If another message occurs, click the Yes button.)

q. Close the Client Types table.

Open the Property table in Design View, create a multiple-field index as described below:

r. Name the index PropertyInformation. (Hint: Be careful not to include spaces after the index name.)

s. Use the PropertyID field, sorted in Ascending order, as the first field in the index.

t. Use the ClientNumber field, sorted in Ascending order, as the second field in the index.

u. Save the changes to the table, and then close it.

Create a Datasheet form for the Client table, and then save the form with the name ClientsForm. (Hint: Do not type the period.) Close the form.

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

Import a table from an encrypted database by completing the following steps:

v. Open the encrypted database Support_SC_AC16_9b_PropertyTypes.accdb, available for download from the SAM website, in Exclusive mode, using the password property when the Enter Password dialog box appears. Enable the content.

w. Decrypt the Support_SC_AC16_9b_PropertyTypes.accdb database. (Hint: You will need to use the password property again.) Close the database.

x. Open (or return to) the SC_AC16_9b_FirstLastName_2.accdb database. (Hint: If the Main Menu Navigation Form appears, close the form.)

y. Import the PropertyType table from the database Support_SC_AC16_9b_PropertyTypes.accdb into a new table with the name PropertyType. (Hint: Ensure that the Tables tab is selected in the Import Object dialog box.)

z. Do not save the Import steps.

Use the Table Analyzer to analyze the Property table for redundancy. Implement the following options when using the Table Analyzer Wizard:

aa. Select the Property table as the table to analyze, and let the wizard decide what fields go in each table.

ab. Rename Table 1 as AltProperties using the Rename table button, as shown in Figure 3 on the next page.

ac. Rename Table 2 as AltStates using the Rename table button, as shown in Figure 3 on the next page.

ad. Rename Table 3 as AltClients using the Rename table button, as shown in Figure 3 on the next page.

ae. When the wizard displays the next screen with the question, “Do the bold fields uniquely identify each record in the proposed table?”, click the Next button.

af. When the wizard displays the next screen, “Correcting typographical errors for table ‘AltStates’.”, click the Next button. If a dialog box appears, asking if you want to move on, click Yes.

ag. When the wizard displays the next screen, “Correcting typographical errors for table ‘AltCities’.”, click the Next button. If a dialog box appears, asking if you want to move on, click Yes. Ensure that the “No, don’t create a query” radio button is selected, then click Finish to close the Table Analyzer Wizard.

ah. When the Table Analyzer Wizard dialog box appears with the message, “The command or action TileHorizontally isn’t available now”, click OK. Close the AltProperties, AltStates, and AltClients tables.

ai. Confirm that the Navigation Pane matches Figure 4 on the next page.

Figure 3: Table Analyzer Wizard

Figure 4: Navigation Pane with Tables Created in Step 13

Switch to viewing database items by the custom category named “Property Information” in the Navigation Pane. Add the following two forms to the Property Information Forms group:

aj. Property Master Form

ak. Properties by State Form

Confirm the Property Information Forms group matches Figure 5 below.

Add a new group to the Property Information category as described below:

al. Name the new group Property Reports.

am. If necessary, move the Property Reports group so that it appears between the Property Information Forms and the Unassigned Objects group.

an. In the Navigation Pane, add the Basic Property List report to the Property Reports group in the Navigation Pane to match Figure 5 below.

Figure 5: Navigation Pane Viewed by Property Information Category

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 SAM website to submit your completed project.

2