West Coast Outdoor Advertisers
ADMINISTeRING A DATABASE SYSTEM
GETTING STARTED
· Open the file SC_AC16_9a_FirstLastName_1.accdb, available for download from the SAM website.
· Save the file as SC_AC16_9a_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_9a_BillboardTypes.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 Rent. (Hint: Do not type the period.)
b. Select Date as the type.
c. Enter 08/08/2016 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 AdRep table in Design View, and then create a custom input mask for the AdRepNumber field as described below:
d. The AdRepNumber 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 AdRep table still open in Design View, modify the AdRepName 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 Advertiser table in Design View, and then create a single-field index on the AdvertiserName field using AdvertiserName as the index 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 Advertiser table still open in Design View, create a multiple-field index as described below:
j. Name the index AdvertiserLocation. (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 Rentals 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 Deposit field value is always less than or equal to the Cost field value. (Hint: Enter [Deposit]<=[Cost] as the validation rule.) o. Enter Deposit cannot exceed Cost as the validation text. 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.) With the Rentals table still open in Design View, create a multiple-field index as described below: p. Name the index RentalsInformation. (Hint: Be careful not to include spaces after the index name.) q. Use the AdvertiserID field, sorted in Ascending order, as the first field in the index. r. Use the RentalID field, sorted in Ascending order, as the second field in the index. s. Save the changes to the table, and then close it. Create a Datasheet form for the Advertiser table, and then save the form with the name AdvertisersForm. (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: t. Open the encrypted database Support_SC_AC16_9a_BillboardTypes.accdb, available for download from the SAM website, in Exclusive mode, using the password billboard when the Enter Password dialog box appears. Enable the content. u. Decrypt the Support_SC_AC16_9a_BillboardTypes.accdb database. (Hint: You will need to use the password billboard again.) Close the database. v. Open (or return to) the SC_AC16_9a_FirstLastName_2.accdb database. w. Import the BillboardType table from the database Support_SC_AC16_9a_BillboardTypes.accdb into a new table with the name BillboardType. (Hint: Ensure that the Tables tab is selected in the Import Object dialog box.) x. Do not save the Import steps. Use the Table Analyzer to analyze the Rentals table for redundancy. Implement the following options when using the Table Analyzer Wizard: y. Select the Rentals table as the table to analyze, and let the wizard decide what fields go in each table. z. Rename Table 1 as AltRentals using the Rename table button, as shown in Figure 3 on the next page. aa. Rename Table 2 as AltMonths using the Rename table button, as shown in Figure 3 on the next page. ab. Rename Table 3 as AltFacing using the Rename table button, as shown in Figure 3 on the next page. ac. 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. ad. When the wizard displays the next screen, Correcting typographical errors for table AltMonths., click the Next button. Ensure that the No, dont create a query radio button is selected, then click Finish to close the Table Analyzer Wizard. ae. When the Table Analyzer Wizard dialog box appears with the message, The command or action TileHorizontally isnt available now, click OK. Close the AltRentals, AltMonths, and AltFacing tables. af. Confirm that the Navigation Pane matches Figure 4 below, and then save and close the form. ag. 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 Rental Information in the Navigation Pane. Add the following two forms to the Rental Information Form group: ah. Rentals ai. Rentals and Billboards Form Confirm the Rental Information Form group matches Figure 5 below. Add a new group to the Rental Information category as described below: aj. Name the new group Rental Reports. ak. If necessary, move the Rental Reports group so that it appears between the Rental Information Form and the Unassigned Objects group. al. In the Navigation Pane, add the Basic Billboards List report to the new group. Confirm that the Rental Reports group and item in the Navigation Pane match Figure 5. Figure 5: Navigation Pane Viewed by Rental 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.
