Shelly Cashman Access 365 | Module 4: SAM Critical Thinking Project C Connect Marketing Group
Creating Reports and Forms
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_CT4C_FirstLastName_1.accdb as SC_AC365_CT4C_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_CT4C_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. In Layout View, group the ContactsByState report by the State field, and then sort the report by the ContactID field in ascending order to make the data easier to interpret. Save and close the report.
2. Modify the BillingsByState report in Layout View 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. View the report in Print Preview to check that the subtotal and total values are fully displayed.
c. If necessary, resize the subtotal and total controls to display the complete values. Save the report without closing it.
3. Apply conditional formatting to the MonthlyBill column in the BillingsByState report 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. Create a summary report of the NWContracts report in Layout View to focus on the annual and monthly billing averages. Save and close the report.
5. In Layout View of the AccountServices report, 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.
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 using Labels Contacts as the 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. Create a report based on the Companies and Contacts tables as follows 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. Do not add any grouping levels other than the default grouping by the Companies table data.
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. Modify the ContactBirthDate form in Layout View as follows to add missing information and to reorganize the fields:
a. Place all the labels and controls in the Detail section of the form 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. Save and close the form.
Figure 5: ContactBirthDate Form
9. Open the ContactEntry form in Layout View, and then bold the ContactID control and its label to draw attention. Save and close the form.
10. Add the date to the ServicesAndAccounts form in Layout View 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. 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.