Shelly Cashman Access 365 | Module 4: SAM Project A Connect Marketing Group
Creating Reports and Forms
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_4A_FirstLastName_1.accdb as SC_AC365_4A_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_4A_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, you need to prepare reports for managers and create forms that help in making database updates. Open the ContactsByState report in Layout View. Group the report by the State field, and then sort the report by the ContactID field in ascending order to make the data easier to interpret. Do not add any other grouping or sorting options to the report. Close the Group, Sort, & Total pane, then save and close the report.
2. Open the BillingsByState report in Layout View, and modify the report as follows to calculate the total contract and average monthly billing totals:
a. Sum the values in the ContractTotal column and average the values in the MonthlyBill column.
b. Switch to Print Preview to view the report and to check that the values in the subtotal controls and the total controls are not truncated (cut off) vertically.
c. Return to Layout View, and, if necessary, drag the sizing handles for the subtotal control and the total control down to display the complete values. Save the report without closing it.
3. With the BillingsByState report still open in Layout View, apply conditional formatting to the MonthlyBill column to highlight the top billing amounts. If the monthly bill amount is greater than $1,000, display the value in bold, Dark Red font (1st column, bottom row in the Standard Colors palette). Save the report again, display it in Print Preview, confirm that it matches Figure 1, and then close it.
Figure 1: BillingsByState Report
4. Open the NWContracts report in Layout View, and then create a summary report to focus on the annual and monthly billing averages. Save and close the report.
5. Open the AccountServices report in Layout View. Apply the Office theme to this object only so it coordinates with the other reports in the database. Save and close the report.
6. Use the Label Wizard to create mailing labels for the Contacts table as follows to prepare for a mass mailing:
a. Use Avery C2160 as the label size.
b. Use Arial font, 11 point 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.)
c. On the first line of the label, include the FirstName field, a space, and the LastName field.
d. On the second line of the label, include the Address field.
e. 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.
f. Sort the labels by the PostalCode field.
g. Save the report as Labels Contacts (which is the default name). Preview the report, confirm that it matches Figure 3, and then close it.
Figure 2: Prototype Contacts Label
Figure 3: Labels Contacts Report
7. Use the Report Wizard as follows to create a report based on the Companies and Contacts tables to list contacts and their companies:
a. Include the CompanyName and RetailStores fields from the Companies table.
b. Include the ContactID, LastName, City, and State fields from the Contacts table.
c. The data is automatically grouped by the Companies table data, but do not add any other grouping levels.
d. Sort the report by the ContactID field in ascending order.
e. Use the Stepped layout and Portrait orientation.
f. Save the report using CompanyContacts as the report name. Preview the report, confirm that it matches Figure 4, and then close it.
Figure 4: CompanyContacts Report
8. Open the ContactBirthDate Form in Layout View, and then modify the form as follows to add missing information reorganize the fields:
a. Select all 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.
b. Add the DateOfBirth control to the end of the form after the State control as shown in Figure 5.
c. 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: ContactBirthDate Form
9. Open the ContactEntry form in Layout View, and then bold the ContactID control to draw attention to the label and data. Save and close the form.
10. Open the ServicesAndAccounts form in Layout View, and then add the date to the form to document the current date. Use the 14-Sep-29 format for the date and do not include the time. Save and close the form.
11. Use the Form Wizard to create a form based on the Accounts table as follows to provide a form for entering account data:
a. Include the AccountNumber, CompanyID, MonthlyBill, AnnualBill, and ContractTotal fields (in that order) on the form.
b. Select the Columnar layout for the form.
c. Save the form using AccountEntry as the form name. 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 website to submit your completed project.