Shelly Cashman Access 365 | Module 6: SAM Critical Thinking Project C Connect Marketing Group
Advanced Report Techniques
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_CT6C_FirstLastName_1.accdb as SC_AC365_CT6C_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. To complete this Project, you will also need the following files:
Support_AC365_CT6C_Programs.txt
3. With the file SC_AC365_CT6C_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 working with the company database, you need to be able to create professional reports for employees and for people outside the company. Import the data from the text file Support_AC365_CT6C_Programs.txt, and then append the records to the Programs table to add two new programs to the table. The text file is a delimited file with a comma separating each of the five fields in the Programs table. Do not save the import steps.
2. Modify the ServiceList report as follows to add the date and time and resize the report:
a. Add the current date and time to the Report Header section.
b. In the Date and Time dialog box, select the second date format (e.g., 01-Sep-29) and the second time format (e.g., 4:19 PM).
c. If necessary, resize the report so that the right boundary of the report is approximately at the 8" mark on the horizontal ruler. Save the report without closing it.
3. Set properties for the ServiceType Header section in the ServiceList report as follows to fine-tune the display of the data:
a. Change the Repeat Section property to Yes.
b. Change the Force New Page property to Before Section. Save and close the report.
4. Modify the ContactsByCompany report by adding the LastName field to the CompanyID Header section so that the left edge of the control is at the 6" mark on the horizontal ruler. The left edge of the label is just to the right of the CompanyName control. Confirm that the arrangement of the controls matches Figure 1, and then save the report without closing it.
5. In the ContactsByCompany report, format the Detail section and its controls as follows to emphasize the contact information:
a. Group the ContactID, FirstName, LastName, City, and State controls.
b. Bold the controls.
c. Resize the Detail section so it is only as tall as necessary to accommodate its controls (approximately 1.25" tall). Confirm that the report matches Figure 1, and then save and close the ContactsByCompany report.
Figure 1: ContactsByCompany Report in Design View
6. Modify the BillingsByState report as follows to format a calculated control:
a. Change the Text13 label in the Page Header section using Late Fee as the new label name.
b. Format the text box control in the Detail section containing the =[MonthlyBill]*0.05 calculation so that it is displayed with the Currency number format and two decimal places.
c. Align the top of the Late Fee text box with the tops of the other controls in the Detail section. Save the BillingsByState report, switch to Report View, confirm your report matches Figure 2, and then close the report.
Figure 2: BillingsByState Report in Report View
7. Modify the BasicContacts report by changing the Alternate Back Color property for the Detail section to No Color to simplify the design. Save and close the report.
8. Add a subreport to the CompanyAccounts report using the Subform/Subreport Wizard as follows:
a. Add the subreport below the Stores label in the Detail section, at approximately the 1.25" mark on the vertical ruler.
b. Use the Accounts table as the source of the subreport.
c. Select the AccountNumber, MonthlyBill, and AnnualBill fields (in that order) from the Accounts table to add to the subreport.
d. Accept the default link for the subreport.
e. Save the subreport using Accounts subreport as the name.
f. Delete the label associated with the subreport.
g. Align the left side of the subreport with the left side of the Stores label. Save the CompanyAccounts report, switch to Report View, confirm your report matches Figure 3, and then close the report.
Figure 3: CompanyAccounts Report in Report View
9. Modify the ContactsByState report as follows to arrange the controls more logically:
a. Resize the width of the report so the right border is at the 8" mark on the horizontal ruler.
b. Move the PostalCode control (label and text box) so that it appears below the State control in the approximate location shown in Figure 4.
c. Align the City, State, and PostalCode text boxes to the left. Confirm that the report matches Figure 4, and then save and close the ContactsByState report.
Figure 4: ContactsByState Report in Design View
10. Modify the ContactList report as follows to make the report easier to read and to remove extra white space:
a. Bold all the labels in the Page Header section.
b. Change the Can Grow property of the Address control in the Detail section to Yes.
c. Resize the Detail section of the report by dragging the lower boundary of the section to approximately the .5" mark on the vertical ruler. Confirm that the report matches Figure 5, and then save and close the ContactList report.
Figure 5: ContactList Report in Design View
11. Modify the AccountBillingList report as follows to organize the data more effectively:
a. Use the Group, Sort, and Total pane to add a footer section to the Group on CompanyID section.
b. Add a text box control in the CompanyID Footer section.
c. Convert the text box control into a calculated control that averages the AnnualBill field.
d. Format the control so that it displays values using the Currency number format and two decimal places.
e. Change the label using Annual Average as the label name.
f. Reposition the control and its label below the AnnualBill control with the left edge of the text box at the 6" mark on the horizontal ruler. Confirm that the report matches Figure 6, and then save and close the AccountBillingList report.
Figure 6: AccountBillingList Report in Design View
12. Create a new blank report in Design View as follows to list program information:
a. Set the Record Source for the report as the Programs table.
b. Save the report with the name ProgramList, but do not close the report.
c. Add the ProgramID, ProgramTitle, StartDate, and National fields to the report and then reposition them so that the left edges of the four controls are at the 2" mark on the horizontal ruler.
d. Add the title Program List to the Report Header section.
e. Add page numbers to the report at the Top of Page (Header) position, using the Page N of M format and Right alignment.
f. Reduce the height of the Detail section to 2". Confirm that the report matches Figure 7, and then save and close the ProgramList Report.
Figure 7: ProgramList Report in Design View
13. Group the ProgramContactList report by the State field, and then sort the report by the ContactID field in ascending order to organize the records. Do not add any other grouping or sorting options to the report. Save and close the ProgramContactList Report.
14. Modify the AccountServices report as follows to add a calculated control:
a. Add a text box control to the Report Footer section. The left edge of the text box is at the 6.5" mark on the horizontal ruler.
b. Convert the text box control into a calculated control that sums the ContractTotal field.
c. Format the control so that it displays values using the Currency number format and zero decimal places.
d. Change the label using Total as the new label name. Confirm that the report in Design View matches Figure 8, and then save and close the AccountServices report.
Figure 8: AccountServices Report in Design View
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.