Shelly Cashman Access 365 | Module 6: SAM Project A Connect Marketing Group
ADVANCED REPORT TECHNIQUES
· Excel Projects Help
GETTING STARTED
1. Save the file SC_AC365_6A_FirstLastName_1.accdb as SC_AC365_6A_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_6A_Programs.txt
3. With the file SC_AC365_6A_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 file Support_AC365_6A_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 create a new table, and do not save the import steps.
2. Open the ServiceList report in Design View, and then modify the 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-Aug-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. With the ServiceList report still open in Design View, set properties for a report section as follows to fine-tune the display of the data:
a. Select the ServiceType Header, and then display the Property Sheet for this section.
b. Change the Repeat Section property to Yes.
c. Change the Force New Page Property to Before Section. Save and close the report.
4. Modify the ContactsByCompany report as follows to add a missing field:
a. Open the ContactsByCompany report in Design View.
b. Open the field list for the report.
c. Add the LastName field to the CompanyID Header section of the report 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. (Hint: You may need to reposition and resize the LastName control and its label after inserting it into the report.) Confirm that the arrangement of the controls matches Figure 1, and then save the report without closing it.
5. With the ContactsByCompany report still open in Design View, format the Detail section and its controls to emphasize the contact information:
a. Select the ContactID, FirstName, LastName, City, and State controls (text boxes and labels) simultaneously.
b. Group the controls.
c. Bold the controls.
d. 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. Open the BillingsByState report in Design View.
b. Change the Text13 label in the Page Header section using Late Fee as the new label name.
c. 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.
d. 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. Open the BasicContacts report in Design View, and then change the Alternate Back Color property for the Detail section to No Color to simplify the design. Save and close the report.
8. Open the CompanyAccounts report in Design View, and then use the Subform/Subreport Wizard as follows to add a subreport:
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. Ensure that the Choose from a list option button is selected, and accept the default link Show Accounts for each record in Companies using CompanyID for the subreport.
e. Save the subreport as Accounts subreport (which is the default 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. Open the ContactsByState report in Design View, and then modify the 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. (Hint: Align the bottom of the PostalCode control with the bottom of the Address control.)
c. Select the City, State, and PostalCode text boxes simultaneously. Do not select the labels.
d. Align the controls to the left as shown in Figure 4. Confirm that the report matches Figure 4, and then save and close the ContactsByState Report.
Figure 4: ContactsByState Report in Design View
10. Open the ContactList report in Design View, and then modify the 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. Select the Address control in the Detail section, and then change the Can Grow property 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. Open the AccountBillingList Report in Design View.
b. Use the Group, Sort, and Total pane to add a footer section to the Group on CompanyID section.
c. Add a text box control in the CompanyID Footer section.
d. Convert the text box control into a calculated control that averages the AnnualBill field. (Hint: Your control should contain the formula =Avg([AnnualBill]).)
e. Format the control so that it displays with the Currency number format and two decimal places.
f. Change the label using Annual Average as the label name.
g. Reposition the control and its label below the AnnualBill control as shown in Figure 6. (Hint: The left edge of the text box should be located 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. Open the Property Sheet for the report, and then apply the Record Source for the report to 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. Open the ProgramContactList report in Design View. Group the 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 additional 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. Open the AccountServices report in Design View.
b. Add a text box control to the Report Footer section. The left edge of the control (not the label) is at the 6.5" mark on the horizontal ruler.
c. Convert the text box control into a calculated control that sums the ContractTotal field. (Hint: Your control should contain the formula =Sum([ContractTotal]).)
d. Format the control so that it displays with the Currency number format and zero decimal places.
e. Change the label using Total as the new label name. Confirm that the report 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.