GETTING STARTED
- Open the file SC_AC16_4b_FirstLastName_1.accdb, available for download from the SAM website.
- Save the file as SC_AC16_4b_FirstLastName_2.accdb by changing the “1” to a “2”.
- If you do not see the .accdb file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.
- 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 Basic Property List report in Layout View. Group the report by the State field, and then sort the report by the City field in ascending order. Do not add any additional grouping or sorting options to the report. Close the Group, Sort & Total Pane. (Hint: Click the Group & Sort button.) Save and close the Basic Property List report.
- Modify the Client Financial Report to match Figure 1 on the following page by completing the following tasks:
- Open the Client Financial Report in Layout View.
- Sum the values in the AmountPaid column and the Balance column for each state’s clients and for all clients.
- Switch to Print Preview to view the report and to check that the values in the subtotal control and the total control are displayed completely.
- Return to Layout View and, if necessary, expand the size of the subtotal control and the total control so that they appear completely.
Save the report without closing it.
- With the Client Financial Report still open in Layout View, apply conditional formatting to the Balance column. If the balance amount is greater than $3,000, display the value in Bold, Maroon font (6th column, 1st row of the Standard Colors palette).
Save the report again, and then close the Client Financial Report.
Figure 1: Client Financial Report
- Open the Clients by Manager Report in Layout View, and then create a summary report. (Hint: To create a summary report, hide the detail lines.) Save and close the report.
- Open the Manager Contact List report in Layout View. Apply the Office theme to this object only. Save and close the report.
- Use the Label Wizard to create mailing labels for the Manager table, and include the following options:
- Use Avery C2163 as the label size.
- Use Arial font, 11 pt. font size, Light font weight, and Black (1st column, 6th row of the Basic Colors palette) font color with no special font styles for the labels. (Hint: These formatting options may be the default settings for your label.)
- On the first line of the label, include the FirstName field, a space, and the LastName field.
- On the second line of the label, include the Address field.
- On the third line of the label, include the City field, a comma (,), a space, the State field, a space, and the PostalCode field. Your label should match Figure 2 on the following page.
Figure 2: Prototype Manager Label
- Sort the labels by the PostalCode field.
- Save the labels as Labels Manager (which is the default name).
Confirm that your labels match the format shown in Figure 3 below, and then close the Labels Manager report. (Hint: If a message appears warning that some data may not be displayed, click OK.)
Figure 3: Labels Manager Report
- Use the Report Wizard to create the report shown in Figure 4 on the following page. The report is based on the Client and Property tables and should include the following options:
- Include the ClientNumber and ClientName fields from the Client table.
- Include the PropertyID and Address fields from the Property table.
- The data will automatically be grouped by the ClientNumber field, but do not add any additional grouping levels.
- Sort the report by the PropertyID field in ascending order.
- Use the Stepped layout and Landscape orientation.
- Save the report as Clients and Properties Report. (Hint: Do not include the period.)
Preview the report, and then close it.
Figure 4: Clients and Properties Report
- Open the Manager Contact Form in Layout View, and then modify it by completing the following tasks:
- Select all the labels and controls in the Detail section of the form. (Hint: Do not select the form title label in the Form Header section.)
- Place the selected controls in a Stacked control layout.
- Add the StartDate control after the PhoneNumber control as shown in Figure 5 on the following page.
- Move the LastName control after the FirstName control as shown in Figure 5. (Hint: Be sure to select both the label and the control for the LastName field.)
Save and close the form.
Figure 5: Manager Contact Form
- Open the Property Update Form in Layout View, and then bold the Date control. Save and close the form.
- Open the Client Types Entry Form in Layout View, and then add the current date to the form. Use the option button for the second date format. Do not include the time on the form. Save and close the form.
- Open the Client Address Form in Form View, and then use the Advanced Filter/Sort option to create a filter with the following properties:
- The records in the form should be sorted in ascending order by the ClientName field.
- The form should only return records in which the State field value is equal to DE.
- Toggle the filter to confirm it is working. (Hint: There should only be four records included in the form.)
Save and close the form.
- Use the Form Wizard to create a form based on the Property table with the following options:
- Include the PropertyID, Address, City, State, and ClientNumber fields (in that order) on the form.
- Select the Columnar layout for the form
- Save the form as Basic Property Form. (Hint: Do not include the period.)
Close the form.
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.
