Jan's Working with Databases

Step-by-Step: Subreports

---

What you will learn:

to copy and paste existing reports to be parent report and subreport to edit subreport to add a subreport using drag-and-drop to report's footer to create a new report to create a subreport with the Subform/Subreport tool to edit subreport to hide duplicate records to remove unwanted white space with Can Shrink

---

Start with:  , resource files, worldtravel-Lastname-Firstname.accdb from folder databases project4 as updated in the previous lesson

Copy and Paste Existing Reports to be Parent Report and Subreport

You will modify two reports that you created earlier. One will be the main report and another will be the subreport.

1. In the Navigation Pane, select the report Trips by Agents, which you created in a previous lesson.

   This report shows the agents with a list of their clients and the cost of each trip, after applying any discount. You saved several versions of this report while learning to create totals. This first version does not include any totals and is missing some formatting that you added later. You will add a subreport that shows the totals.

   Problem: Report does not match illustration
   Solution: Make changes to Trips by Agents to match. The Group Header should have a light blue background with the controls in the Group Header Back Style set to Transparent. The Detail section has a white background. All controls have Border Style set to Transparent.

2. Copy and paste it back to the Navigation Pane with the name Agent Trips with subreport totals.
   
3. In the Navigation Pane, select the report Trips by Agents - Query Totals.
4. Copy and paste it to the Navigation Pane with the name Subreport-Agents Trips Totals.
   

---

Edit Subreport

You will modify one of your new reports to make it work better as a subreport about totals.

1. Open your new report Subreport-Agents Trips Totals in Design View.
   This report will be eventually be put in the Report Footer of the main report. You can remove a lot of parts. You only need to see the agent's name and the group header (Staff.LastName header), and the Detail section.

2. Delete controls and labels from Page Header, Staff.LastName Header, and Detail sections.

3. Resize those sections to zero height by dragging the bottom edge upward.

   

4. Move the label and control for the total in the group footer and the one in the Report Footer to the left.
5. Change the font size in those labels and controls to 12 pt.
6. Resize the labels and controls to Best Fit.
7. Switch to Print Preview.
   Whoops. Several things need fixing.
   • We forgot about the dividing lines that were in the original report.
   • The Grand Total does not line up nicely.
   • The name of the travel agent is nowhere to be seen!
8. Return to Design View.
   The dividing lines do not really show.
9. Select the dividing line in the Detail section by dragging across the area where it should be.
   A gold border with handles appears.
10. Press the DELETE key to remove the line.
11. Repeat for the dividing line in the Report Footer.

12. Size the control for the Grand Total value (in blue) to be the same as the one for the agent (in green) and align them evenly.

    

13. Align the left edges of the labels Total for Agent and Grand Total.
14. Delete the label Total for Agent.
15. Use the Text Box control tool to add a new control at the left of the green total.
16. Delete the label for the new control.
17. Select the new unbound control.
18. In the Control Source property, type ="Total for "&[Staff.LastName]
    This will work well for now since we know that there are not two agents who have the same last name. If that is a possibility, you would need to use a full name instead.
19. Change the Border Style to Transparent.

20. Switch to Print Preview.
    Much better.

21. Save the report and close it.
    [Subreport-Agents Trips Totals]
    

---

Create Subreport Control with Drag-and-Drop

1. Open the other new report, Agent Trips with subreport totals in Design View.
2. Format the labels in the group header as Green, bold, 14 pt.
   
3. Drag the report Subreport-Agents Trips Totals from the Navigation Pane over to the report.
   The mouse pointer change to the Report shape.
   

4. Drop it at the left of the Report Footer.
   The Report Footer gets taller to hold the subreport but the subreport control is still too short to show much in Design View.

   Access keeps the control short but automatically set the property Can Grow to Yes. When Access figures out what to show in Print Preview, it can set the height to the correct value to show everything.
   

5. Switch to Print Preview.
   Just a few things to make neater.

   • The label for the subform is not readable and really is not necessary.
   • The title for the subreport should say something about Totals.
   • The subreport is wider than necessary and is falling off the page at the right. That will make an extra page print.
6. Switch to Design View.
   
7. Select the subreport.

8. Delete the label for the subreport.
   It lies just above the edge of the subreport. When you select it, you will see its borders.

   Next you need to edit the subreport itself.

   But, the subreport control is too short for you to see most of the subreport.
   Editing this is best done in the Design View of the original report. There is a button for that!

9. In the ribbon Report Design Tools: Design, click on the button Subreport in New Window , which is at the far right of the ribbon.
   The subreport opens in a new tab while the main report stay open.
10. Edit the title of the subreport to read Totals for Agents.
11. Change the title's font size to 16 pts.
12. Delete the controls in the Page Footer.
    They don't show in the subreport anyway.
13. Drag the report's right edge to the 4" grid line, which is wide enough to hold the controls that are left.

14. Close and save the subreport.
    [Subreport-Agents Trips Totals]

    You are back in Design View of the main report but the subreport shows just as a white rectangle.

    The subreport control is the same size as before. Resizing the report does not resize the control!

15. If necessary, select the subreport control.
16. Change the Width property to 4".
17. Check the width of the report.
    If it is wider than 8", resize it to just 8".
18. Add to the page footer:
    • An unbound control on the left with the expression =Now()
      This control will show the current date. Format the date as Long Date, text aligned Left, Border Style as Transparent.
    • A label to the center of the Page Footer with your name.
    • Use the Page Numbers button on the Report Design Tools: Design ribbon tab to insert Page N of M style page numbers at the right.
19. Save the main report.
    [Agent Trips with subreport totals]
    
20. Print. (There should be only 1 page.)

    Problem: Report uses 2 pages
    Solution: Resize the report to be 8" wide or less.

21. Close the report.

    You have completed your first report that has a subreport!
    Next you will create a report that has a subreport in the Detail section.
    

---

Create Agent Report

You will create a new report with information about agents and a new subreport that lists the agent's clients. The subreport will go in the Detail section of the main report.

The database already has forms that show all of the information about an agent. You will include only a few fields about the agent in this report so we can get to the subreport more quickly.

The previous lesson on subforms left us with an awkward listing of clients on the Agents with Clients form. A client showed up once for each trip. The duplication of names cannot be resolved easily for a form, but it can be fixed in a report. But first you will have to create the report!

1. Start a new report with the Report Design button on the Create ribbon tab.
   A blank report opens in Design View.
   
2. In the Property Sheet in the property Record Source, click the ellipsis button .
   The Query Builder opens.
3. Add the Staff table and the query QFullName-Staff.
4. If necessary, create a join between the two field lists by dragging the StaffID field from one and dropping it on the StaffID field in the other.
5. Drag the * field for the Staff table to the grid and the field SFullName from the query QFullName-Staff.
6. Drag the fields LastName, FirstName, Middle Name, and Title from the Staff table to the grid, in that order.
7. Uncheck the Show box for these last three fields.
8. Sort the last three fields Ascending.
   

9. In the Title column type the word "Agent" in the Criteria row.

   

   Now the query will include all of the fields about Staff but only records for Agents. The records will be sorted in normal alphabetical order for names.

10. Click the Close button on the ribbon tab and save the changes to the SQL statement.
    You are back in the report's Design View.
11. Show the Field List by clicking the Add Existing Fields button on the ribbon tab.
12. Drag and drop the following fields onto the Detail section of the blank report in the positions shown in the illustration:
    • SFullName
    • Location
    • Photograph
13. Set the Height for the Detail section to 3".
14. Edit the label SFullName to read just Name.
15. Select the controls for SFullName and Location and set the Width property to 2" wide.
16. Change the Text Align property to Left.
17. Set the Border Style property for the three controls in the Details section to Transparent.
18. Delete the label for the Photograph.
19. Resize the Photograph control to 1.5" x 1.5".
20. Right click on a section bar and select Report Header/Footer to show those sections.
21. Click on the Title button on the ribbon tab and enter Agents with list of Clients.
22. Save the report with the name Agents, with Clients.

23. Switch to Print Preview.
    There is a lot of extra space that gives you room for the subreport.
    
    

 

---

Create Subreport with Subform/Subreport Tool

You do not have to have a report already created to put a subreport into your report. When you use the Subform/Subreport tool from the Controls palette, the Subreport wizard will appear.

Thinking: What fields do you need to show an agent's clients in a subreport?

• Client's name
• A field to connect the subreport to the main report.
• Fields for the parts of the client's name to use to sort the clients' names into alphabetical order.
  (The report will ignore the order in the original query.)

1. Switch to Report Design View.
   
2. Click on the Subform/Subreport tool on the Controls palette.
   
3. Drag in the white space below the Location control to create a new subreport.
   The subreport wizard opens.
   
   
4. Select to use an existing Tables and Queries.
5. Click on Next >.
   
   
6. Select the following tables and queries and move the named fields to the right with the > button.
   • table Clients: fields LastName, FirstName, MiddleName
     These are the fields that can be used to sort the clients' names
   • table Staff: field StaffID
     This field is needed to link the subreport to the main report.
   • query QFullName-Client: field CFullName
     This is what we actually want to see in the subreport.
7. Click on Next >.
   

8. Accept the default suggestion for how to link the report and subreport: Show Staff for each record... using StaffID.

   The wording here is a bit tricky to understand. We want to see the client's names for a particular agent. What connects the main report to the subreport is StaffID from the Staff table. Remember you had to filter the Staff table to show just the people with a title of "Agent" for this report.

   But the Clients table does not have a field that corresponds to StaffID. So what connects a client to a particular agent? It's all about Relationships! You already defined a relationship between Clients and Trips and between Trips and Agents. Access knows this and automatically connects the Agent with the Client for whom the agent did a trip. Sweet!
   

9. Click on Next >.
   
10. Enter the name Subreport-Clients for Agent.
    

11. Click on Finish.
    Access creates a subreport control and a subreport that contains all of the fields.
    

    The subreport control starts at the top left of the rectangle you created with the Subform/Subreport but widens to show the full width of the subreport.

    This subreport makes the report too wide for one sheet of paper. You do not need all of these fields to show.

    The Navigation Pane shows the new subreport, Subreport- Clients for Agent.

12. Save the report.
    [Agents, with Clients]
    

---

Edit Subreport

1. If necessary, select the subreport control.
   This time there is enough room to edit in place.
   
2. Right click on the Report Header and in the context menu, click on Report Header/Footer to toggle the command off.
   
   A message appears warning that deleting sections will also delete controls in those sections. This action is not in the Undo list.
3. Click on Yes.
   The Report Header and Footer sections vanish.
4. Repeat for the Page Header and Footer sections.
   
5. Delete all of the controls in the Detail section except CFullName.
6. Drag CFullName to the top left of the section.
7. Drag the bottom edge of the report up to the bottom of the CFullName control.
   There should be no white space above or below the control.
8. Scroll sideways to see the right edge of the subreport.
9. Drag the edge to the left until it bumps into the CFullName control.
   There should be no white space to the left or right of the control.
10. Click out of the subreport.
11. If necessary, drag the subreport control to line up its left edge with the left edge of the controls above and leave some white space above the subreports label.
    

12. Resize the subreport control's width by dragging its right edge to the left, leaving the control just as wide as the CFullName control in it.

13. Use the Property Sheet to set the Height property for the subreport to 0.5" .
    Set the Height property for the Detail section to 1.7"

    The subreport control will show scrollbars in Design View even though there is nothing out of view.

    You have two choices about what to do if there are more clients than will fit in the subreport control. You can let the subreport control grow or you can let it show vertical scrollbars.
    

14. Click the Select Report button at the top left of the subreport.
    (Be sure you are selecting the subreport!)

15. In the Property Sheet for the subreport, change the property Scroll Bars to Vertical Only.
    This will leave the subreport control at its current height, no matter how many or how few clients there are to list.

    The extra wide subreport made the whole report wider than 8". It won't fit on a page width now.

16. Drag the right edge of the main report to the 8" grid line.
    

17. Switch to Print Preview.
    It takes two pages to show the seven agents. Only three agents have clients in the database yet.
    

    There may be some problems left to deal with.

    Issue: The subreport for Hector Chavez grew taller instead of showing a scrollbar. The subreport control automatically set the Can Grow property to Yes.

    Issue: The subreport for Hector Chavez, the first agent, lists Eric Bjorenson four times. He must have booked four trips. You will hide the duplications in the next section.

    Issue: The clients are not alphabetized. For the agent Wilhelm Heinz, D G Garcia is listed twice, with someone else in between.

    You will have to fix these issues to get the duplicates to hide.

18. Save the report.
    [Agents, with Clients]

---

Hide Duplicate Records

Report controls have a property that form controls do not: Hide Duplicates. When this property is set to Yes, duplicate entries will not show. But... there several other things that have to be set correctly or you won't get what you expected.

• Records must be sorted so that duplicates are next to each other.
  That's the only way Access can tell that there are duplicates. It's smart but not that smart!
• Hide Duplicates is set to Yes for the control you want to hide.
• There is no extra space above or below what you are hiding. Otherwise the list of clients will have unexpected spacing.
  (The layout already does this.)
• Can Shrink property must be set to Yes for:
  • Detail section of the subreport
  • Control in the subreport

Fix Sorting

1. Switch to Design View for the report.
2. On the ribbon tab Report Design Tools: Format, in the Selection tab group, choose Subreport-Clients for Agents in the drop list.
   This is another way to select part of a report or form.
3. Click the Select Form button at the upper left of the subform.
   A black square shows on the button and the Property Sheet show properties for the form.
   

4. Open the Record Source with the ellipsis button .

   

   The Query Builder opens.
   It shows the tables, queries, and fields that you chose in the Subreport wizard. But it also shows the Trips table! You did not pick that one. Access did something smart. It added the Trips table in order to join the Staff and Clients tables together.

5. Sort Ascending for the fields LastName, FirstName, and MiddleName.
   Make sure the fields are in that order.
6. Close Query Builder and save the changes.

Hide Duplicates for Control

1. Select the control CFullName.
2. Change the property Hide Duplicates to Yes.
3. Change the property Can Shrink to Yes.
   This will keep Access for showing empty space when a record is hidden.
4. Click on the Detail section bar in the subform.
5. In the Property Sheet, change the Can Shrink property to Yes. 
6. Set the Alternate Back Color to No Color.
7. In the parent form, move the label for the subreport to the left in alignment with the Location label and, if necessary, move the subreport control down to not overlap its label.
8. Select the subreport control and set its Can Shrink property to Yes.
9. Click on the Detail section bar in the parent form and set the Can Shrink property to Yes.
10. In the Page Footer, add a label with your own name on the left.
    

11. Switch to Print Preview.
    Is this better?

    The subreport control shows the clients' names in proper alphabetical order and does not list anyone twice for the same agent. Hurrah at last!

    Problem: Extra space or background color
    The subreport form has extra space above or below the control CFullName.
    Solution: Edit the form to remove the extra space.
    

12. Save the report.
    [Agents, with Clients]
13. Print page 1.

14. Switch to Layout View.

    Unexpectedly, the duplicate entries show as blank space in this view. Don't let that scare you. The Print Preview is still fine.
    

15. Close the report