Microsoft Access has a built-in Wizard to help you create reports. After the wizard creates a report from your record source, most often, you will want to make changes. The source for the report can be a query or a table. The great thing about using a query is that you can pull information from more than one table and line up the data that you want. Hi, this is Crystal This is the Relationships Diagram for our sample database, which is composed of sales data from musical instrument sales. The ProdCats table is for product categories. Products is the actual product name. In the case of musical instruments, the price for a product varies too much to specify a default price. ProdSales is the transactional sales data. Please realize this is a simplified example so there is no brand, year, or other values that would normally be specified. Create a new query by choosing Query Design from the CREATE ribbon tab. I often close the Show Tables dialog box and drag tables, or queries, from the Navigation Pane. In the sample database that can be downloaded, choose Products, ProdSales, and ProdCats. Rearrange the tables in the upper pane so that the join lines are clear. From the product sales, double-click on the date of the sale to put it on the grid below. The grid shows what information will be used for the query. From ProdCats, the ProdCat field contains the name of the category. Double-click to add it to the grid. From Products, the prodName field contains the name of the product. Double-click to add it to the grid. Double click the Amount field in the product sales table to add it also. Finally, we want to add key fields so that that when a menu with criteria is used, matching can be done more efficiently. This database uses an AutoNumber Primary Key. In related tables, the foreign key field is a Long Integer data type with no Default Value and it’s named the same. Save the query and call it qSales. I am using the Save icon on my customized Quick Access toolbar, which I am showing below the ribbon To learn how to customize the Quick Access Toolbar, there are other videos on this channel you can watch. Links are in the video description. To see what this data looks like, click on the Datasheet View icon, which you will find on both the Design and Home ribbon tabs. I also have it on my customized QAT. When looking at data, you may want to select all the columns and do a best-fit. Unlike Excel, which best-fits the whole sheet, even what is not showing, Access best-fits only the data that is displayed. Now that data we want to be on the report is lined up in a query, use the Report Wizard on the CREATE ribbon tab to make a report that we will then change. The first step is to specify where data will come from. If the last thing you did before launching the wizard was to click on a table, or query, in the Navigation Pane, it will be chosen under Tables/Queries. If not, drop the list and pick it. Since the query shows everyting that is wanted, click the button to move all the fields to the Selected Fields list (>>). The next step asks for how you want to view your data. Choose ProdSales, which is probably the default value selected. This is an extra step if you are used to the older wizards. The next step is to specify how fields will be grouped. The first levels will be year, month, and day, which are all calculated from the sales date field. Double-click the date of the sale three times. After date groupings, the daily sales for product categories will be summed, so double-click on that field. Click on the Grouping Options command button in the lower left. Specify Year for the first date group. Month is the default, so the second date group does not need to be changed. The third date group will be by Day. The product category will be a Normal grouping, as opposed to grouping by the first letter or inital specified number of letters. Click OK to close the Grouping Intervals dialog box and then Next to continue with the Wizard steps. After the grouping, you can specify how the report will be sorted. The difference between grouping and sorting is that groups can have subtotals and other calculations. Sort by the product name in Ascending order, and then the amount of the sale in Descending order so that the largest values will be listed first. When you click the Summary Options button, you can set statistics to be calculated. Click on the checkbox to Sum the Amount. Notice that other statistics can also be put in by the wizard. Anything the wizard does can be modified later. Click OK and then Next. I like to choose Outline for the Layout as it requires less time to change. The next dialog box asks for the title of the report. This will also be the report name in the Navigation Pane. Change to “Product Sales” and click the Finish button to Preview the data for the report. In another lesson, we will change the report name. This report isn’t very pretty … yet .. but we can fix that. In design view, you can do editing. The labels for the groupings, dtSale by Year, dtSale by Month, and dtSale by Day are not necessary as the value tells you what it is. When the control showing Amount is not wide enough to display the whole number, you see number signs (###) for values. Spacing is also loose — we can tighten it and use less space to display the information. Switch to the design view of the report. Right-click on the report and choose Design View from the shortcut menu. First, let’s move the title of the report to the Page Header section so it will show on every page, not just once in the beginning. Select it and drag it when the mouse changes to a 4-headed arrow to the Page Header section. If the Page Header and Footer sections are not displayed, you can right-click in another section that is not on a control and choose Page Header/Footer Show_PageHeaderFooter.jpg Because the wizard does not make the text true black, on the Design ribbon, click text color for letters and choose Black from the Standard colors (so they won’t be changed with the Theme). The Page Header section is displayed but it does not have any space. Make space in the Page Header section by moving your mouse to the bottom border. When the cursor changes to a horizontal line with a double-headed vertical arrow, click and drag the boundary. In the Report Header section, Double-click the control (on a resizing handle) with the report title to do a best-fit. Label controls are best-fit for height and width – but this varies between monitors and printers; so you may want to make it a bit wider for other devices. Now that space is created, move the title label to the upper left corner of the Page Header section. Close the extra space in the Page Header by dragging its bottom boundary up. Move the control with page number information to the Page Header section. Line up the right boundary with 6.5 inches on the horizontal ruler above the design canvas. When a control is selected, look at the Rulers to see where it is. If Rulers are not showing, right-click in a section that is not a control and choose Ruler. form_section_ShortcutMenu The wizard puts date and page number information in different controls. We want to combine them. Zoom the control source by clicking in the (control source) property and pressing Shift-F2. The first time you open the Zoom box for the Access session, you may want to change the size of text to be bigger. Click the Font… button in the lower right and set the properties. Change the equation to:=”Printed ” & Now() & “, Page ” & [Page] & ” of ” & [Pages] Anything enclosed in double quote marks will be shown literally, like “Printed ” and “, Page “. Be sure to add spaces and puctuation in the quoted string. Now() is a function that returns the current date and time. [Page] is an Access variable for the current page number [Pages] is an Access variable for the total number of pages Since the equation is right-aligned in the control, drag the left edge of the countrol boundary further to the left to show more. Delete the labels in the group header sections since the data is descriptive enough to understand what it represents. The rectangle object can also be deleted, for the same reason. Later, in another lesson, these values will be staggered. Close up the extra space in each of the sections by dragging the bottom of the section up. Select multiple controls by clicking and dragging on the ruler. Every control within the dragged selection will be selected when you let go of the mouse. We are selecting these controls to set the font to be bold. In the product categories header section, delete the label controls. Again, the information does not need explanation. Move the product category to the top of the section by setting Top to be zero. The Left property will be 0.2 so it is staggered under the date. Close up the extra space in the section by dragging the bottom of the section up. Select all the detail controls by clicking in the horizontal ruler where they are located. Set the Top property to 0 (zero) to reduce extra space. Set the Left property of the product name to 0.4″ (change from audio) on the Property Sheet. The date of the sale and the key fields do not need to show. Select them and then on the Format tab of the Property Sheet, set Visible to No. When controls are hidden, it is a good idea to make that obvious to developers who open in design view so set the back color to be dark and the fore color to be light. Make the width of each control wide enough to show what it contains and close the extra space, taking care that the hidden controls do not go past what we want to be the right boundary. With Amount and all the summed amount controls in each of the sections, set Left to 3″ and Width to 1.5” Delete unnecessary controls and close space. Select label controls in each of the sections with summaries by clicking and dragging and shift-clicking and dragging to add to the selection. Best fit all controls in the selection by double-clicking on a resizing handle. Then set the Left property to 0 to make them all move to the left edge, and the border to Transparent. There are controls which go past 6.5 inches. Select them and set width to 6.5 inches, since they are all lined up at the left. Drag the right boundary of the form in as far as it will go. Close up extra space and Save. When I look at the report, I see that I failed to close up extra space in the Detail Section. Go back to the Design View and do that. Ahh, that looks better. The Report View is displayed without the page breaks of Print Preview. Print Preview is useful to see what you will get when you print. Report View is useful when pages will be displayed on the screen, provided there is no VBA code that needs to execute when sections are formatted. Scrolling to the end of the report, we can see several subtotals. They are not well labeled. In a future lesson, we will fix that. To read an article about this video, go to the October 2016 issue of Strategic Finance. A link is in the video description. Thanks for joining me. Through sharing, we will all get better.