BIS 245 Guided Project 6

Guided Project 6-1 For this project you enhance the functionality of the database for a friend’s music collection. Recall that in Guided Project 4-1 you used the Report Wizard to create a report that shows all the songs on each album. For this project you create an improved report that is grouped by artist and shows all the albums and song details. You use Design view to create the main report and the subreport and customize the report in Design view to add sections add and edit controls and add a running total. Finally you create and customize a clustered bar chart in a new report. [Student Learning Outcomes 6.4 6.5 6.6 6.7 6.8] File Needed: MusicDatabase-06.accdb (Available from the Start File link.) Completed Project File Name: [your name]-MusicDatabase-06.accdb Skills Covered in This Project This image appears when a project instruction has changed to accommodate an update to Microsoft 365 Apps. If the instruction does not match your version of Office try using the alternate instruction instead. Requirements: Project Complete Create a main report in Design view. Edit a report in Design view. Add a group section to a report. Add and edit a control in a report. Create a subreport using the Report Wizard. Add a subreport to a main report. Add totals to a subreport. Concatenate text data. Add totals to a main report Create and customize a chart in a report. Open the MusicDatabase-06 database start file. The file will be renamed automatically to include your name. Change the project file name if directed to do so by your instructor Enable content in the security warning. Build the main report. To provide context for the steps in the project view the completed report in Figure 6-118.Click the Report Design button [Create tab Reports group] to open a new report.Click the Property Sheet button [Design tab Tools group] and the Format tab if necessary.Click the Property Sheet button [Report Design Tools Design tab Tools group] and the Format tab if necessary.Enter 7″ in the Width property.Click the Data tab.Click the drop-down arrow in the Record Source property and select the Albums table.Click the Add Existing Fields button [Design tab Tools group] to open the Field List.Click the Add Existing Fields button [Report Design Tools Design tab Tools group] to open the Field List.Double-click the AlbumName and Artist fields to add them into the Detail section.Save the report as SongListingOnAlbumsByArtist. Click the Report Design button [Create tab Reports group] to open a new report. Click the Property Sheet button [Design tab Tools group] and the Format tab if necessary.Click the Property Sheet button [Report Design Tools Design tab Tools group] and the Format tab if necessary. Enter 7″ in the Width property. Click the Data tab. Click the drop-down arrow in the Record Source property and select the Albums table. Click the Add Existing Fields button [Design tab Tools group] to open the Field List.Click the Add Existing Fields button [Report Design Tools Design tab Tools group] to open the Field List. Double-click the AlbumName and Artist fields to add them into the Detail section. Save the report as SongListingOnAlbumsByArtist. Add a group section to the report.Click the Group & Sort button [Design tab Grouping & Totals group] to open the Group Sort and Total pane at the bottom of the report.Click the Group & Sort button [Report Design Tools Design tab Grouping & Totals group] to open the Group Sort and Total pane at the bottom of the report.Select the Add a group option in the Group Sort and Total pane.Select the Artist field from the field list.Click the More button in the Group Sort and Total pane to display the available options (Figure 6-114).Figure 6-114 Group Sort and Total paneChange the without a footer section option to with a footer section.Change the do not keep group together on one page option to keep whole group together on one page.Close the Group Sort and Total pane by clicking the X on the Group Sort and Total pane header. Be careful not to click the Delete button inside the pane or you will remove the group.Save the changes made to the report. Click the Group & Sort button [Design tab Grouping & Totals group] to open the Group Sort and Total pane at the bottom of the report.Click the Group & Sort button [Report Design Tools Design tab Grouping & Totals group] to open the Group Sort and Total pane at the bottom of the report. Select the Add a group option in the Group Sort and Total pane. Select the Artist field from the field list. Click the More button in the Group Sort and Total pane to display the available options (Figure 6-114).Figure 6-114 Group Sort and Total pane Change the without a footer section option to with a footer section. Change the do not keep group together on one page option to keep whole group together on one page. Close the Group Sort and Total pane by clicking the X on the Group Sort and Total pane header. Be careful not to click the Delete button inside the pane or you will remove the group. Save the changes made to the report. Modify the report.Move your pointer to the bottom edge of the Page Header section. The pointer changes to the resize arrow. Move the resize arrow up to the Page Header bar to remove the space in the section.Right-click inside the report to open the context menu.Select the Report Header/Footer option to add Report Header and Report Footer sections to the report.Click inside the Report Header section.Press F4 to open the Property Sheet and click the Format tab if necessary.Enter .5″ in the Height property.Click inside the Artist Header section.Enter .3″ in the Height property.Follow the same steps to set the Height property of the Artist Footer section and Page Footer section to .25″ if necessary and the Detail section to 2″.Add a label control into the Report Header section and type Songs on My Albums into the label.Select the label and enter 1.8″ in the Width property .25″ in the Height property .15″ in the Top property 2.7″ in the Left property 14 in the Font Size property select Bold in the Font Weight property and Yes in the Font Italic property.Click the Page Numbers button [Design tab Header/Footer group].Click the Page Numbers button [Report Design Tools Design tab Header/Footer group].Select the Page N of M radio button the Bottom of Page [Footer] radio button and Center alignment.Click OK.Select the Artist text box. Press and hold the Shift key and select the Artist label. Both controls should be selected.Drag the selected controls into the Artist Header section. Click anywhere to deselect both controls.Select the Artist text box and enter 2.5″ in the Width property .03″ in the Top property and 1.5″ in the Left property and select Transparent in the B Style property. When entering property values don’t worry if Access slightly changes a few of the values (for example .03″ becomes .0299″).Select the Artist label and enter Recording Artist in the Caption property 1.1″ in the Width property .03″ in the Top property and .35″ in the Left property.Select the AlbumName label and enter Album in the Caption property .5″ in the Width property .05″ in the Top property and .35″ in the Left property.Select the AlbumName text box and enter 3.5″ in the Width property .05″ in the Top property and 1″ in the Left property and select Transparent in the B Style property. The report should look similar to Figure 6-115.Figure 6-115 Design view of the main reportSave and close the report. Move your pointer to the bottom edge of the Page Header section. The pointer changes to the resize arrow. Move the resize arrow up to the Page Header bar to remove the space in the section. Right-click inside the report to open the context menu. Select the Report Header/Footer option to add Report Header and Report Footer sections to the report. Click inside the Report Header section. Press F4 to open the Property Sheet and click the Format tab if necessary. Enter .5″ in the Height property. Click inside the Artist Header section. Enter .3″ in the Height property. Follow the same steps to set the Height property of the Artist Footer section and Page Footer section to .25″ if necessary and the Detail section to 2″. Add a label control into the Report Header section and type Songs on My Albums into the label. Select the label and enter 1.8″ in the Width property .25″ in the Height property .15″ in the Top property 2.7″ in the Left property 14 in the Font Size property select Bold in the Font Weight property and Yes in the Font Italic property. Click the Page Numbers button [Design tab Header/Footer group].Click the Page Numbers button [Report Design Tools Design tab Header/Footer group]. Select the Page N of M radio button the Bottom of Page [Footer] radio button and Center alignment. Click OK. Select the Artist text box. Press and hold the Shift key and select the Artist label. Both controls should be selected. Drag the selected controls into the Artist Header section. Click anywhere to deselect both controls. Select the Artist text box and enter 2.5″ in the Width property .03″ in the Top property and 1.5″ in the Left property and select Transparent in the B Style property. When entering property values don’t worry if Access slightly changes a few of the values (for example .03″ becomes .0299″). Select the Artist label and enter Recording Artist in the Caption property 1.1″ in the Width property .03″ in the Top property and .35″ in the Left property. Select the AlbumName label and enter Album in the Caption property .5″ in the Width property .05″ in the Top property and .35″ in the Left property. Select the AlbumName text box and enter 3.5″ in the Width property .05″ in the Top property and 1″ in the Left property and select Transparent in the B Style property. The report should look similar to Figure 6-115.Figure 6-115 Design view of the main report Save and close the report. Build the subreport.Click the Report Design button [Create tab Reports group] to create a new report.Press F4 to open the Property Sheet and click the Format tab if necessary.Type 6″ in the Width property.Click the Data tab.Click the drop-down arrow in the Record Source property and select the Songs table.Click the Add Existing Fields button [Design tab Tools group] to open the Field List.Click the Add Existing Fields button [Report Design Tools Design tab Tools group] to open the Field List.Add the SongTitle and Length fields into the Detail section. The Length text box should be selected.Press and hold the Shift key and select the SongTitle text box.Click Tabular [Arrange tab Table group].Click Tabular [Report Design Tools Arrange tab Table group].Press F4 to open the Property Sheet and click the Format tab if necessary.Enter .02″ in the Top property.Right-click inside the report to open the context menu.Select the Report Header/Footer option to add Report Header and Report Footer sections to the report.Select both label controls in the Page Header section.Click the Move Up button [Arrange tab Move group].Click the Move Up button [Report Design Tools Arrange tab Move group].Enter .2″ in the Height property and .02″ in the Top property.Right-click inside the report to open the context menu.Select the Page Header/Footer option to delete the Page Header and Footer sections.Click the Report Header section bar and enter .25″ in the Height property.Follow the same steps to set the Height property of the Detail and Report Footer sections to .25″ if necessary. The report should be similar to Figure 6-116.Figure 6-116 Design view of the subreportSave the report as SongsSubreport.Close the report. Click the Report Design button [Create tab Reports group] to create a new report. Press F4 to open the Property Sheet and click the Format tab if necessary. Type 6″ in the Width property. Click the Data tab. Click the drop-down arrow in the Record Source property and select the Songs table. Click the Add Existing Fields button [Design tab Tools group] to open the Field List.Click the Add Existing Fields button [Report Design Tools Design tab Tools group] to open the Field List. Add the SongTitle and Length fields into the Detail section. The Length text box should be selected. Press and hold the Shift key and select the SongTitle text box. Click Tabular [Arrange tab Table group].Click Tabular [Report Design Tools Arrange tab Table group]. Press F4 to open the Property Sheet and click the Format tab if necessary. Enter .02″ in the Top property. Right-click inside the report to open the context menu. Select the Report Header/Footer option to add Report Header and Report Footer sections to the report. Select both label controls in the Page Header section. Click the Move Up button [Arrange tab Move group].Click the Move Up button [Report Design Tools Arrange tab Move group]. Enter .2″ in the Height property and .02″ in the Top property. Right-click inside the report to open the context menu. Select the Page Header/Footer option to delete the Page Header and Footer sections. Click the Report Header section bar and enter .25″ in the Height property. Follow the same steps to set the Height property of the Detail and Report Footer sections to .25″ if necessary. The report should be similar to Figure 6-116.Figure 6-116 Design view of the subreport Save the report as SongsSubreport. Close the report. Add the subreport to the main report.Open the SongListingOnAlbumsByArtist report in Design view.Click the Subform/Subreport button [Design tab Controls group].Click the Subform/Subreport button [Report Design Tools Design tab Controls group].Click inside the Detail section of the report close to the left b below the Album label. The SubReport Wizard launches.Select the Use an existing report or form radio button. The SongsSubreport report should be highlighted in the list.Click Next.Accept the Choose from a list radio button and the Show Songs for each record in Albums using AlbumID statement and click Next.Accept the suggested name for the subreport and click Finish. The subreport is added into the main report.Switch to Layout view (Figure 6-117). Verify that the subreport is correctly linked to the main report.Figure 6-117 Layout view of the main report after adding the subreportSave the report. Open the SongListingOnAlbumsByArtist report in Design view. Click the Subform/Subreport button [Design tab Controls group].Click the Subform/Subreport button [Report Design Tools Design tab Controls group]. Click inside the Detail section of the report close to the left b below the Album label. The SubReport Wizard launches. Select the Use an existing report or form radio button. The SongsSubreport report should be highlighted in the list. Click Next. Accept the Choose from a list radio button and the Show Songs for each record in Albums using AlbumID statement and click Next. Accept the suggested name for the subreport and click Finish. The subreport is added into the main report. Switch to Layout view (Figure 6-117). Verify that the subreport is correctly linked to the main report.Figure 6-117 Layout view of the main report after adding the subreport Save the report. Customize the main report and subreport.Switch to Design view.Click to select the SongsSubreport label.Press Delete.Select the subreport.Enter .4″ in the Top property 1″ in the Left property and select Transparent in the B Style property.Save and close the report. Switch to Design view. Click to select the SongsSubreport label. Press Delete. Select the subreport. Enter .4″ in the Top property 1″ in the Left property and select Transparent in the B Style property. Save and close the report. Open the SongsSubreport in Design view.Open the Property Sheet and click the Format tab if necessary.Select both the Length text box and label.Enter .5″ in the Width property.Select both the SongTitle text box and label.Enter .2″ in the Left property and 4.7″ in the Width property.Select the Report Footer section bar and enter .3″ in the Height property.Add a text box control to the Report Footer section.Click the Data tab and then click the Build button on the Control Source property.Type =Count([SongTitle]) into the Expression Builder and click OK.Click the Format tab and enter .35″ in the Width property .2″ in the Height property .02″ in the Top property and 4.6″ in the Left property of the text box.Select the label control of that text box.Enter Total number of songs: in the Caption property 1.55″ in the Width property .2″ in the Height property if necessary .02″ in the Top property and 3″ in the Left property.Save and close the report. Open the Property Sheet and click the Format tab if necessary. Select both the Length text box and label. Enter .5″ in the Width property. Select both the SongTitle text box and label. Enter .2″ in the Left property and 4.7″ in the Width property. Select the Report Footer section bar and enter .3″ in the Height property. Add a text box control to the Report Footer section. Click the Data tab and then click the Build button on the Control Source property. Type =Count([SongTitle]) into the Expression Builder and click OK. Click the Format tab and enter .35″ in the Width property .2″ in the Height property .02″ in the Top property and 4.6″ in the Left property of the text box. Select the label control of that text box. Enter Total number of songs: in the Caption property 1.55″ in the Width property .2″ in the Height property if necessary .02″ in the Top property and 3″ in the Left property. Save and close the report. Open the SongListingOnAlbumsByArtist report in Design view.Click the Detail section bar and enter 1.4″ in the Height property.Add a text box control to the Artist Footer section.Delete the label control of that text box.Select the text box and on the Data tab click the Build button in the Control Source property.Type =”Total number of albums by ” & [Artist] & “:” into the Expression Builder and click OK.Click the Format tab and enter 3.3″ in the Width property .2″ in the Height property .02″ in the Top property .7″ in the Left property and select Right in the Text Align property.Add another text box control to the Artist Footer section.Delete the label control of that text box.Select the text box and on the Data tab click the Build button in the Control Source property.Enter =Count([AlbumName]) into the Expression Builder and click OK.Click the Format tab and enter .2″ in the Width property .2″ in the Height property .02″ in the Top property and 4.1″ in the Left property and select Left in the Text Align property.Enter .25″ in the Height property of the Artist Footer section.Save the report.Switch to Print Preview view. The completed report should look similar to Figure 6-118. Notice how Adele’s name appears only once at the top of both of her albums. This is because Artist is the grouping header.Figure 6-118 Print Preview view of completed reportNavigate to page 5 and then 6 of the report. Notice how Cristian Castro’s data starts on the top of page 6 instead of near the bottom of page 5. This is because keep the whole group together on one page is selected in the grouping options.Click the Close Print Preview button.Close the report. Click the Detail section bar and enter 1.4″ in the Height property. Add a text box control to the Artist Footer section. Delete the label control of that text box. Select the text box and on the Data tab click the Build button in the Control Source property. Type =”Total number of albums by ” & [Artist] & “:” into the Expression Builder and click OK. Click the Format tab and enter 3.3″ in the Width property .2″ in the Height property .02″ in the Top property .7″ in the Left property and select Right in the Text Align property. Add another text box control to the Artist Footer section. Delete the label control of that text box. Select the text box and on the Data tab click the Build button in the Control Source property. Enter =Count([AlbumName]) into the Expression Builder and click OK. Click the Format tab and enter .2″ in the Width property .2″ in the Height property .02″ in the Top property and 4.1″ in the Left property and select Left in the Text Align property. Enter .25″ in the Height property of the Artist Footer section. Save the report. Switch to Print Preview view. The completed report should look similar to Figure 6-118. Notice how Adele’s name appears only once at the top of both of her albums. This is because Artist is the grouping header.Figure 6-118 Print Preview view of completed report Navigate to page 5 and then 6 of the report. Notice how Cristian Castro’s data starts on the top of page 6 instead of near the bottom of page 5. This is because keep the whole group together on one page is selected in the grouping options. Click the Close Print Preview button. Close the report. Create a report for the chart.Click the Report Design button [Create tab Reports group] to open a new report.Press F4 to close the Property Sheet if necessary.Save the report as AlbumDataChart. Click the Report Design button [Create tab Reports group] to open a new report. Press F4 to close the Property Sheet if necessary. Save the report as AlbumDataChart. Create a clustered bar chart.Click the Insert Modern Chart button [Design tab Controls group] to display the list of available charts.Click the Insert Modern Chart button [Report Design Tools Design tab Controls group] to display the list of available charts.Select Bar from the list and then select Clustered Bar from the additional set of available charts.Move the pointer into the Detail section of the report and click to insert the chart. Click the Insert Modern Chart button [Design tab Controls group] to display the list of available charts.Click the Insert Modern Chart button [Report Design Tools Design tab Controls group] to display the list of available charts. Select Bar from the list and then select Clustered Bar from the additional set of available charts. Move the pointer into the Detail section of the report and click to insert the chart. Customize the chart data.Click Data in the Chart Settings pane if not already selected.Select the Tables radio button in the Data Source section.Click the drop-down arrow in the Data Source box and select Albums.Select Genre as the Axis (Category) field.Select AlbumID as the Values (Y axis) field.Hover over the AlbumID field and then click the arrow to display the list of aggregation choices.Select Count. Click Data in the Chart Settings pane if not already selected. Select the Tables radio button in the Data Source section. Click the drop-down arrow in the Data Source box and select Albums. Select Genre as the Axis (Category) field. Select AlbumID as the Values (Y axis) field. Hover over the AlbumID field and then click the arrow to display the list of aggregation choices. Select Count. Modify the chart properties.Click the Property Sheet button [Design tab Tools group].Click the Property Sheet button [Report Design Tools Design tab Tools group].Enter 4.5″ in the Width property 4″ in the Height property .5″ in the Top property and 1″ in the Left property.Select No in the Has Legend property.Enter Albums by Genre in the Chart Title property.Enter 16 in the Chart Title Font Size property.Enter Number of Albums in the Primary Values Axis Title property.Press F4 to close the Property Sheet.Save the report. The completed chart should look similar to Figure 6-119.Figure 6-119 Design view of completed Albums by Genre chart Click the Property Sheet button [Design tab Tools group].Click the Property Sheet button [Report Design Tools Design tab Tools group]. Enter 4.5″ in the Width property 4″ in the Height property .5″ in the Top property and 1″ in the Left property. Select No in the Has Legend property. Enter Albums by Genre in the Chart Title property. Enter 16 in the Chart Title Font Size property. Enter Number of Albums in the Primary Values Axis Title property. Press F4 to close the Property Sheet. Save the report. The completed chart should look similar to Figure 6-119.Figure 6-119 Design view of completed Albums by Genre chart Close the report. Save and close the database. Upload and save your project file. Submit project for grading.