Shelly Cashman Access 365 | Module 6: End of Module Project 1 TopStream, Inc.

GETTING STARTED

1. Save the file SC_AC365_EOM6-1_FirstLastName_1.accdb as SC_AC365_EOM6-1_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_EOM6-1_Producers.txt

3. With the file SC_AC365_EOM6-1_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. TopStream, Inc. is an online streaming platform for TV shows, movies, podcasts, music, and videos. As the new projects coordinator, you are in charge of introducing TopStream's services in the Pacific Northwest. You are using an Access database to track and store company data for that region, and are developing reports to provide formatted collections of the data. You just hired two new producers and need to add them to the Producers table. Import the data from the file Support_AC365_EOM6-1_Producers.txt, and then append the records to the Producers table. The text file is a delimited file with a comma separating each of the five fields. Do not save the import steps. The Producers table should contain 21 records after the import. Save and close the table.

2. Your manager asks you to modify a report so that each state prints on a separate page. Open the MembersByState report in Design View and change the Repeat Section property for the State Header section to Yes. Change the Force New Page property to Before Section. Save but do not close the report.

3. With the MembersByState report still open in Design View, add the current date and time to the Report Header section. Use the 01-Aug-29 format for the date and the 4:19 PM format for the time. Resize the report so that its right boundary is at the 8.5" mark on the horizontal ruler. Save and close the report.

4. Open the ContributorFees report in Design View to finish designing the report. Change the Text11 label in the Page Header section using Total Fees as the new caption for the label control. Resize the label to display the entire caption, if necessary. Format the text box containing the =[RegistrationFee]+[MonthlyFee] calculation so that it displays values as currency with 2 decimal places. Save the report, and then switch to Report View. Confirm that your report matches Figure 1, and then close the report.

Figure 1: ContributorFees Report in Report View

5. Open the MediaAndMembers report in Design View, and then add a subreport based on the Members table to display related member data. Include the MemberID, FirstName, and LastName fields in the subreport. Link the main report to the subreport using the default link and save the subreport as Members subreport, which is the default name. If necessary, reposition the subreport so that its top-left edge is at the 1" mark on the vertical ruler and the 1" mark on the horizontal ruler. The bottom-right edge of the subreport control is at 2" mark on the vertical ruler and the 7.25" mark on the horizontal ruler. Confirm that the report matches Figure 2, and then save and close the report.

Figure 2: MediaAndMembers Report in Design View

6. Open the MonthlySubscriptions report in Design View to include subscription subtotals for each state. Add a text box control to the State Footer section. The left edge of the text box is at the 7" mark on the horizontal ruler. Convert the text box control into a calculated control that sums the MonthlyFee field using the formula =Sum([MonthlyFee]). Format the control so that it displays values as currency with 0 decimal places. Change the label for the new text box using Subtotal as the caption. Confirm that the report matches Figure 3, and then save and close the report.

Figure 3: MonthlySubscriptions Report in Design View

7. Use Report Design View to create a new report for the Subscriptions table. Save the report using SubscriptionList as the report name. Include the SubID, SubDescription, RegistrationFee, and MonthlyFee fields in the report. The left edges of the four text box controls should be at the 3" mark on the horizontal ruler. Separate the fields vertically by a single dotted line in the design grid. Add Subscription List as the title for the report. Add page numbers to the report in the Page Header section, using the Page N of M format and Right alignment. Confirm that the report matches Figure 4, and then save and close the report.

Figure 4: SubscriptionList 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.