Chapter 5. Creating PivotTables
PivotTables are a great way to analyze a large amount of data. They operate on a table that contains raw data and produces a table of summary data. A PivotTable allows you to summarize information in a variety of ways. This ability to view the same data from multiple perspectives gives PivotTables their name. PivotTables were designed to rotate, or pivot, the data around a particular aspect. Consider a cylinder, for example. From the end it looks like a circle, while from the side it looks like a rectangle. A PivotTable provides graphical tools to quickly change perspectives so you can determine which views are most useful.
In this chapter I'll show you how to create a PivotTable and demonstrate many of the features in Access 2010 for using and customizing them.
Slicing, Dicing, and Drilling
In most applications, you will often have one or more large tables that you'll need to provide some way to extract information from. For example, in our Library project, the Loan table can be quite large. It has a record for each time an item is checked out. Even in a modest-sized library, you could have thousands of records created each day. You can analyze a large amount of data more effectively by breaking it down into various views that each describes a different aspect of the data.
A PivotTable starts with a value that you need to report. This could be the total sales, number of customers, or average response time. This value is expressed as a statistical computation such as Sum, Count or Average. You could provide a single value for the entire data set; however, most of the time you'll want to see that value expressed as a function of some other attribute. For example, "total sales per day" or "average response time for each type of request."
This technique is called slicing; the entire data set is cut into slices with each slice representing an attribute value such as date or request type. The value (total sales or response time) is them computed for each slice. For example, you could slice the Loan table using the media type. This would allow you to see how books or videos are moving. Likewise you could slice the table by the category to see whether Children's or Classics were more popular.
You'll often need to slice the data in multiple ways simultaneously. Suppose you wanted to determine the most popular media type for each category. This is what it means to dice the data. (When you cut a tomato in one direction you get slices, but when you cut it in multiple directions you get diced tomatoes.) The value is then computed for each combination of attribute values, such as Seasonal DVDs or Children's Books.
If you were to graph these values, you might display the media type on the X-axis and the category on the Y-axis. Because of this, each attribute that is used to dissect the data is sometimes referred to as a dimension. A dimension is an aspect that you'll use to slice the data. Media types and categories are good candidates for dimensions. With a PivotTable, you can dice your data in a theoretically unlimited number of directions; however, it is difficult to present more than two or three dimensions at a time. As you'll see in this chapter, Access 2010 has some creative ways to visually present multiple dimensions.
Often you need to drill into your data, which is to increasingly narrow your focus. Let's say, for example, that want to see when your busiest times are. You can start by finding the busiest days and then drill down by hours to determine more specific time periods. Drilling is analogous to taking a single slice and cutting it into smaller pieces. You can take one of the smaller pieces and further dissect it, if necessary.
NOTE
PivotTables are provided in other tools, such as SQL Server Reporting Services (SSRS) and Excel. The concepts are similar in each tool, but there are differences in their implementation and the specific features that are provided.
Creating a PivotTable View
A PivotTable is not actually a table, but a specialized view of a table. You've already used the Datasheet View, Design View, and SQL View. In Access, you'll use the PivotTable View to display an existing table. As I explained in Chapter 4, you can also use a select query just like a table, so you can use the PivotTable View on a query as well.
There are two important implications to this approach.
First, all the columns that you want included in the PivotTable must be in a single table or query. If you've done a proper normalized table design (see Chapter 2), your tables will not likely work too well for this purpose. Instead, you will probably need to create a query that joins all the necessary tables, as explained in Chapter 4.
Second, Access does not actually store the summarized data that is displayed in a PivotTable. When you save a PivotTable View, Access saves the view's configuration, not the data. You may notice a pause when you open a PivotTable View, because Access has to load the entire table or query and re-calculate the summary information. Also, you cannot edit the summarized data. When data in the underlying table is changed, the PivotTable View is not automatically updated, but it can be easily re-summarized. I'll show you how to do that later in the chapter.
Also in this chapter, you'll use the AllLoans query that you created in Chapter 4. It provides all the loan details that you'll need for your PivotTable. So let's open the PivotTable View.
1. Open the AllLoans query, which should open in the default Datasheet View.
2. Click the dropdown button under the View button in the ribbon. This will display all the available views.
3. Select the PivotTable View link, as shown in Figure 5-1.
Figure 5.1. Selecting the PivotTable View
You can also click the PivotTable View button in the lower-right corner of the application, as shown in Figure 5-2.
Figure 5.2. Selecting the available views
Understanding the PivotTable Layout
The initial blank PivotTable is shown in Figure 5-3. To design it, you'll add fields from the underlying table – in this case, the AllLoans query – to one of four areas.
Figure 5.3. The initial blank PivotTable
The large section in the center is where you'll place the value(s) that will be summarized. This is referred to as the Data area. Each record from the AllLoans query will fall into one of the cells in this area. Typically, each cell represents an aggregate function of the records assigned to that cell. For example, the cell could be the sum of OverdueFee, the count of LoanID, or the maximum DaysOverdue. In this area, you'll specify the field and the aggregate function such as sum, count, and max, to be applied to that field. The fields that are placed in this section are called Totals fields, because they are often sums or counts. The more generic name Detail field is also used, because not all aggregate functions produce totals.
The areas above and to the left of the main section contain the Column and Row fields, which are used to specify which cell each record is assigned to. For example, the Column field could be CategoryDescription and the Row field could be CheckedOut. Access finds the appropriate cell for each record by looking up its values for these fields. Each cell represents a unique combination of category and the date the item was checked out.
The section at the very top contains Filter fields. As I mentioned, you can only effectively display two dimensions at a time. The Filter field provides a third dimension, but does so by limiting the view based on the value of that field. For example, if you used MediaDescription, you could analyze one media type at a time. You could also select any subset of media types, including all of them.
You should also see a PivotTable Field List window, which is shown in Figure 5-4. This shows you all the columns in the AllLoans query. If this window is not currently visible, click the Field List button in the Design ribbon.
Figure 5.4. The PivotTable Field List window
To design the PivotTable, simply drag the desired columns from this window to the appropriate areas on the PivotTable View.
Using the PivotTable Fields
This PivotTable will be used to analyze what items are being checked out, so the first thing you'll specify is the data values that will provide that information. This will define what the view is summarizing. In this case, you'll present the number of items that have been loaned out. A Loan record is created each time an item is borrowed, so you'll simply count the number of records. To do that, use the LoanID field and the Count function, as follows.
1. Drag the LoanID field to the data area. The view will look similar to Figure 5-5.
Figure 5.5. Adding the LoanID field
2. By default, the PivotTable View shows the details by listing all the records that are represented in each cell. Because no aggregate function has been specified yet, the data area will show no totals. Select the LoanID field in the data area and then click the AutoCalc button in the Design ribbon and select the Count link as shown in Figure 5-6.
Figure 5.6. Selecting the Count aggregate function
3. This will add a row to the data area that represents the Count of loans. Click the Hide Details button in the ribbon to suppress the details. The PivotTable View will now show only the total number of loans, as shown in Figure 5-7.
Figure 5.7. The total loan count
4. In my database, I have loaned out a total of 11 books. The column heading "Count of LoanID," while accurate, is probably not the best presentation. To change this, right-click this cell in the data area and select the Properties link. Select the Captions tab and enter Loans in the Caption field, as shown in Figure 5-8.
Figure 5.8. Changing the field caption
Notice that you can also adjust other properties such as the font through the Properties dialog box.
Defining the Column Field
Now you're ready to define the dimensions that will be used to evaluate the number of loans. Start by dragging the CategoryDescription field to the Column section. The PivotTable View will now include a column for each unique value of CategoryDescription. Notice that there is now a column for Childrens, Classics, Reference, and Seasonal, as shown in Figure 5-9. There is also a Grand Total column that presents the overall total from all categories.
Figure 5.9. Adding the CategoryDescription to the Column area
NOTE
The PivotTable View does not necessarily list every category defined in the Category table; it only displays the categories that are represented in the AllLoans query. For example, I also have a Fiction category that is not shown, because no item from this category has been loaned yet.
There is a small "+" and "-" button under each column header. You can use these to show the details for each cell. In my database, there are three loans for items in the Reference category. By clicking the "+" button, you can see that LoanID 9, 11, and 12 were for Reference items, as shown in Figure 5-10.
Figure 5.10. Displaying the details of a cell
Using Date Fields
The CheckedOut field records the date/time when the item was checked out. Drag this to the Row section. Because you're the only user, no two items can currently be checked out at exactly the same time. Because each Loan record will have a different value in the CheckedOut column, you'll end up with a row for each item, as shown in Figure 5-11.
Figure 5.11. Adding the CheckedOut field
Date fields are actually a composite of several values, such as year, month, day, and so on. This makes them very useful in a PivotTable View. Notice in the PivotTable Field List window, that the CheckedOut By Month and CheckedOut By Week fields are included in addition to the CheckedOut field. Similar fields were also added for the DueDate field.
Remove the CheckedOut field from the PivotTable View by right-clicking on the field and selecting the Remove link. You can also select the field and click the Remove Field button in the Design tab of the ribbon. Drag the CheckedOut By Month field to the Row section. Expand the Years, Quarters, Months, and Days columns to drill into the data, as shown in Figure 5-12.
Figure 5.12. Drilling into the date properties
This is an ideal example of drilling into your data. Starting with the total for the year, you can then expand a specific quarter and month to see the daily totals. The "+" and "-" button on each summary row allow you to expand or collapsed that particular value.
Notice the dropdown indicator next to the Years column label. If you click this you'll see the dialog box shown in Figure 5-13. This allows you to select the years that you want included in this view.
Figure 5.13. Selecting the years to include in the view
This is only allowed at the top level of the date hierarchy. The same dropdown button is displayed next to the CategoryDescription column. In the same way you can choose to limit the categories that will be included in the view.
TIP
The CheckedOut By Week field works just like CheckedOut By Month, except the hierarchy is presented as Year, Week, and Day of Week. Months do not divide into weeks very well. This field divides each year into 52 weeks, but does not group by quarter or month. (Every 5 or 6 years has 53 weeks.) Likewise, the CheckedOut By Month field divides a year into quarters and months, but does not group into weeks. Both will present totals by year and day. You'll need to use one or the other depending on whether the quarter/month or week grouping is more useful for your analysis.
Adding a Filter Field
Drag the MediaDescription field to the Filter area. The Filter area is used to limit the records that are represented in the view. By default, the All value is selected, so this doesn't affect the results in the PivotTable View. Click the dropdown button, which will list the values that can be selected as shown in Figure 5-14.
Figure 5.14. Selecting the filter values
Unselect the All option and then select DVD video. The PivotTable View should look like Figure 5-15.
Figure 5.15. Restricting the view to only include DVD Video items
If you select more than one value for the filter, under the MediaDescription column heading it would display "(Multiple Items)."
Refreshing the PivotTable
As I mentioned earlier, a PivotTable is a summarized snapshot of your underlying table. It is not automatically updated as data is added or modified. This is done primarily for performance reasons. The summary data is recalculated when the PivotTable view is opened. Usually, when doing data analysis, you're not concerned with real-time, up-to-the-minute data, so this is a reasonable compromise.
If you need to re-calculate the data PivotTable based on the current data, just click the Refresh Pivot button on the Design tab of the ribbon, as shown in Figure 5-16.
Figure 5.16. Using the Refresh Pivot feature
Adding Other Fields
So far, I've covered the basic usage of a PivotTable View. You specify a field that contains the data values (count of LoanID in this case) and then add fields to the Column, Row, and Filter sections that allow you summarize the data using these attributes. Now I'll show you how to extend this functionality.
Including Multiple Values
The view currently has a single data point that represents the number of loans. You can add additional data points. Suppose you also wanted to know how much was charged in overdue fees. To do this, simply add the OverdueFee field to the Data area.
1. In the PivotTable Field List window, select the OverdueFee field and then select the Data Area, as shown in Figure 5-17.
Figure 5.17. Adding OverdueFee to the Data area
2. Click the Add to button to add this to the PivotTable View. The Sum function is used by default, which is correct for this field, because you want the total amount of overdue fees that were charged. However, the caption is somewhat wordy.
3. Right-click this new column in the Data area and change the caption to Fees as shown in Figure 5-18.
Figure 5.18. Changing the field caption
The modified PivotTable View should look like Figure 5-19.
Figure 5.19. The PivotTable View with a second data field
Using Calculated Columns
Now you'll add one more data value, which is the number of items that were (or are) overdue. The current AllLoans query does not have a field with that information, however. So you'll need to first modify the query and add a calculated column.
Open the AllLoans query and switch to the Design View by using the View button in the Design ribbon. Scroll to the first empty column in the lower pane of the Design View. Right-click in the Field row and select the Build link. Enter the following formula:
Overdue: −1*((Not IsNull([CheckedIn]) And [CheckedIn]>[DueDate]+1) Or (IsNull([CheckedIn])
And [DueDate]<Now()-1))
An item is considered overdue if it was checked in after the due date or if it is not yet checked in but the due date is in the past. The formula uses the IsNull function to determine if the item has been checked in. Because the item is not considered overdue until midnight on the due date, the formula must add a day to the due date. (The DueDate field does not contain a time portion; see Chapter 3 for details.)
In Visual Basic, a Boolean field has a numeric value of 0 when false, and −1 when true. To account for this, the formula multiples the result by −1. So every record will have a value of 1 if overdue and 0 if not. The PivotTable View can simply sum this new column to get a count of overdue items.
Switch to the Datasheet View and verify the values of this column are correct for each record. Then go back to the PivotTable View and you should see the new Overdue field in the PivotTable Field List window. Add this field to the Data area just like you did with the OverdueFee field. Change the caption of this column to Overdue. The PivotTable View should look like Figure 5-20.
Figure 5.20. The PivotTable View with a third data field
Adding Additional Column Fields
In the same way, you can also add additional columns to either the Column or Row area. For example, instead of using the MediaDescription field in the Filter area, you can move it to the Column area. Select the MediaDescription column in the Filter area and drag it over the PivotTable View. Notice that a blue marker will appear when the mouse pointer is over an area that will accept this field. Drag it to the right of the CategoryDescription column, as shown in Figure 5-21.
Figure 5.21. Dragging the MediaDescription field to the Column area
Now, instead of only displaying a single media type at a time, the MediaDescription field used is a sub-grouping under the CategoryDescription field, as demonstrated in Figure 5-22.
Figure 5.22. MediaDescription as a sub-grouping under CategoryDescription
For the Classics category, the PivotTable View now shows the total loans, fees, and overdue items for each media type. It also displays a total across all media types. You can also collapse each of the categories to only display the total, as shown in Figure 5-23.
Figure 5.23. Collapsing each of the categories
This hierarchy of attributes functions much like the CheckedOut By Month field that you added to the Row area. You can collapse or expand specific values to drill down into the data. Using this approach, you can add any number of fields to the Column or Row areas
TIP
You added the MediaDescription field to the right of the CategoryDescription field. This caused the media type to be a secondary level under category. If you had dragged it to the left the CategoryDescription, then the media type would be the top-level in the hierarchy.
Creating a Field Hierarchy
When planning these multi-level groupings, you should try to use attributes that are logically related. The date field is the perfect example. Months and weeks are logical subdivisions of a year as are days, hours and minutes. So it is logical to drill down from year to month to day. However, categories and media types are orthogonal attributes.
When using multiple fields in a Column or Row area you have to specify the order in which the fields are used in the hierarchy. In this case the category was first so you can see how a summary for each category and then drill down to see what media type were loaned from each category. By placing media type as a sub-group under category, you cannot view a media type across all categories.
Someone may prefer to see how a specific media type was represented in each category. You cannot accomplish both at the same time. When you have a situation like this where you need to summarize in both directions, then you should put one attribute in the Row area and the other in the Column area.
A better design would be to move the CheckedOut By Month field to the Filter area and move the MediaDescription field to the Row area. Make those adjustments in your PivotTable View and it should look like Figure 5-24.
Figure 5.24. Rearranging the fields in the PivotTable View
Notice that you can simultaneously see both category and media type summaries, as well as how the other attribute was represented in the total. Click the dropdown button next to the CheckedOut By Month field in the Filter area. The dialog box shown in Figure 5-25 will appear which you can use to filter the view to include only specific months or days, for example.
Figure 5.25. Selecting the dates to include
Remember, the Filter field(s) define the set of records that are included in the PivotTable View. By placing the CheckedOut By Month field here, you can control the time period that you want to analyze. Date fields are often a good candidate for a Filter field. However, if you want to see an attribute tracked over time, you will generally use the appropriate date field in Row area.
In the current database schema, there is a single-level categorization of items. For a large library you would probably want multiple levels, such as the Dewey Decimal system. The Dewey Decimal system defines three main levels of organization, which are referred to as classes, divisions, and sections. It is called a decimal system because there are 10 classes that each has 10 divisions, which, in turn, has 10 sections each. Thus there are 100 divisions and 1000 sections.
If you were to implement a structure such as this, then this would be an excellent candidate for using a multi-level field hierarchy in your PivotTable View. Look for other fields that logically fit into a hierarchy. The Author and Title fields are another good example.
Using the PivotChart View
Once you have defined a PivotTable View, you can easily turn this into a graphical presentation. Select the PivotChart View button from the lower right corner or select the PivotChart View link from the View button in the ribbon. The default settings will probably look something like Figure 5-26.
Figure 5.26. The initial PivotChart View
Configuring a PivotChart View
This is a fairly complex PivotTable with several values to chart. Each value is displayed as a bar in this bar graph. To see what each bar represents, you'll need to display the legend. Click the Legend button in the Design tab of the ribbon. The legend will look similar to Figure 5-27.
Figure 5.27. The PivotChart legend
The problem with this chart is that it is trying to display too many values. In a bar chart, the Column field(s) are used to define the bar legend (a color for each column) and the Row field(s) are used for the X axis. Click the "Switch Row/Column" button, which will transpose the Row and Column fields. The resulting chart should now look like Figure 5-28.
Figure 5.28. Switching the Row and Column fields
This is a little bit easier to follow. For the Classics category, for example, you can now see how each media type contributes to the summary totals Loans, Fees, and Overdue. Notice the dropdown button next to the CategoryDescription and MediaDescription fields. You can use these to filter the records that are included in the chart. Click the CategoryDescription dropdown and unselect all of the categories except Classics, as shown in Figure 5-29.
Figure 5.29. Selecting only the Classics category
Changing the Chart Type
With the data limited to a single category, you may want to use a different type of chart. Click the "Change Chart Type" button in the ribbon. Select the Line type shown in Figure 5-30.
Figure 5.30. Selecting a Line chart
The resulting chart is shown in Figure 5-31.
Figure 5.31. The PivotChart View using a line chart
You can see from this chart that overdue fees have been charged for Hardback books, but no fees have been charged for DVD videos or paperback books even though there have been overdue items of these media types.
NOTE
You will likely have different values in your database. My purpose here is to give you an example of how to read the chart.
Now display the PivotTable View by selecting the PivotTable link from the View button. Notice that this view now looks very different from where you left it. This illustrates a very important point: The PivotTable View and the PivotChart View use the same Row, Column, and Filter area definitions. If you change this configuration, it is automatically changed in the other, as well. More subtle and perhaps more significant, you can only have one PivotTable/PivotChart View for each table or query.
WARNING
You can define only one PivotTable/PivotChart View for each table or query. The PivotTable and PivotChart views share the same configuration; when you change one, the other is also updated.
Exporting a PivotTable View to Excel
Access 2010 allows you to export a PivotTable or PivotChart view to Excel. This will allow you to manipulate the data and view using the Excel application. As I mentioned earlier, the concepts are similar but the implementation is different.
From the PivotTable View, click the CategoryDescription dropdown and select all categories. Save the database and then click the "Export to Excel" button in the Design tab of the ribbon. You will see a pop-up window warning you about possible compatibility issues, shown in Figure 5-32. Click the OK button.
Figure 5.32. Compatibility warning
Access will then open the Excel application and create a PivotTable worksheet, as shown in Figure 5-33. Notice the similarities to Access 2010 and the differences.
Figure 5.33. The PivotTable View in Excel
The most obvious difference is that the multi-field hierarchy is not as evident. Notice that the CheckedOut By Month field is replaced by a set of fields, one for each of the data components such as Year, Quarter and Month.
The raw data is also imported in the second worksheet, as shown in Figure 5-34.
Figure 5.34. The underlying Excel data
Excel uses the raw data that the PivotTable is based on. In order for the PivotTable to work, all of the raw data must be imported, as well. You can use this approach create a new PivotTable and/or chart in Excel using the data from Access and the PivotTable features from Excel.
WARNING
When you export data to Excel, this is a one-time operation. Subsequent changes to the data in Access are not pushed to Excel using manual or automatic means. The only way to update Excel is to re-import the data from Access.
Summary
PivotTables and charts are an excellent way to present and analyze data from your Access database. You configure a PivotTable or chart by adding one or more fields from the underlying table to each of the following areas:
Data area: This contains the values that ate presented and normally use an aggregate function to summarize the data.
Row and Column: Fields in these areas define the attributes that are used for slicing and dicing the data.
Filter: (Optional) Fields in the Filter are used to limit the data that is being presented.
If you specify multiple Row or Column fields, they are added in hierarchical fashion allowing you to drill down from one attribute to the next. The built-in By Month and By Week date fields are an excellent example of this.
The most significant limitation to keep in mind is that you can only have one PivotTable/PivotChart View for each table or query. The PivotTable and PivotChart views share the same configuration values; when you change one, both are updated. If you want to create additional PivotTables, refer to Chapter 4 for instructions on how to create a query that joins several tables.
You can also export the data and PivotTable to Excel and manipulate the presentation using the Excel tools.
Part III. Creating Forms and Reports
In Part 2, you created your database, designed the tables, and wrote data macros to implement many of the business rules. You created queries to provide de-normalized views into your data, and even designed a pivot table to analyze the data that is being collected. Although still a bare-bones solution, your database fulfils all the basics requirements. In Part 3, however, you'll put flesh on those bones and create a rich user experience.
Chapter 6 will show you how to use the built-in form templates to easily generate many of the simpler forms. Chapters 7, 8, and 9 demonstrate how to build more complex forms from scratch. This will demonstrate a lot of handy tricks for creating useful forms. Chapter 10 will show you how to create menus and navigation pages that will guide the user to the provided features. In Chapter 11, you'll focus on the visual, branding aspects of your application, including themes, graphics, and background images.
Finally, in Chapter 12, I'll show how to create reports, which are essentially forms that are designed for print output.
Chapter 6. Standard Forms
One of the really great features of Access is that it can generate forms for you based on your table design. This is another good reason for starting with a well thought out data schema. In this chapter, I'll demonstrate several common form patterns that will satisfy many of your UI requirements. These are created using standard form templates or the Form Wizard, and do not require writing any code.
This chapter will also provide a foundation that applies to both these simple forms as well as advanced custom forms. In subsequent chapters, I'll show you how to design forms yourself and use macros and VBA code to implement more complex solutions. These custom forms are based on the same general principles that I will explain in this chapter.
Creating a Single Form
We'll start by creating a simple form to display records in the Category table.
Using the Form Wizard
From the Create tab of the ribbon, click the Form Wizard button. In the first dialog box of the Form Wizard, select the Category table, and then move the CategoryCode and CategoryDescription fields to the Selected Fields list, as shown in Figure 6-1.
Figure 6.1. Selecting the record source for the form
Each form is based on a single table or query, and the first step in designing a new form is to specify which will be the source for this form. After selecting a table or query from the dropdown list, all the available fields will display. You can then select all of the fields to be included on the form or just a subset of them.
WARNING
If your form will be used to add records, you should generally use a table for the source, rather than a query. Queries usually supply only a subset of fields or rows and often use multiple tables. All of these characteristics are problematic when inserting records. That is not to say that you cannot use a query; however, if you use a query for a form that allows new records, make sure that every required field is included on the form or has a default value assigned. Otherwise, the form will not be able to save a new record.
Notice that the primary key was not included in this form; this is a common design practice. As I discussed in Chapter 2, the primary key is a surrogate key generated by the database engine to ensure uniqueness. In many cases it is not meaningful to the end user, so there is no need to display it in that scenario.
In the second dialog, you'll need to choose how you want the fields organized on the form. As shown in Figure 6-2, there are four layout options available. I will explain these choices later in this chapter. Because this form only has two fields on it, just leave the default option of Columnar and click the Next button.
Figure 6.2. Choosing the desired layout option
In the final dialog box, enter Category for the title of this form. For the radio options, select the first option, "Open the form to view or enter information," as shown in Figure 6-3.
Figure 6.3. Specifying the form name
The new form should look like Figure 6-4.
Figure 6.4. The initial Category form
In this simple form, a single record is shown. Notice the record navigator at the bottom of the form. You can use this to move to the first, previous, next, or last record in the table. The last control in this group will display a blank record for adding a new category.
Using the Available Views
Just like with tables and queries there are several views available when working with a form. To display all the view options, perform the following steps:
1. Click the Design View button in the ribbon. If the Property Sheet is not currently visible, click the Property Sheet in the Design tab of the ribbon. There is a dropdown list at the top of the Property Sheet that you can use to select the object that you want to view. You can select the Form, any of the sections such as Form Header or Detail, or one of the individual controls. Because there are a lot of properties, these are grouped into separate tabs.
2. Select the Form object and the Format tab. There are several properties that control what views are allowed for this form. The Form Wizard generates the form with the Datasheet View turned off. Change this value to Yes as shown in Figure 6-5.
Figure 6.5. Allowing the Datasheet View
Notice that the View options in the ribbon now has four options, which are shown in Figure 6-6.
Figure 6.6. The allowed views
Every form usually has two modes that it can be viewed in; Form View and Datasheet View. You've already seen the Form View. In this view, a single record is displayed and a record navigator control is used to move through the available records. Each field is represented by an appropriate data-bound control, such as Text Box, Check Box, or ComboBox depending on the type of data contained in the field. In addition, Label controls are used to annotate what each data control is for. All of these controls are arranged on the form.
Select the Datasheet View from the ribbon, which should look like Figure 6-7.
Figure 6.7. Displaying the Datasheet View
The Datasheet View of a form looks very much like the table when it is displayed in the Datasheet View. In this view, you can re-order the columns by selecting one and then dragging it to the desired position. You can change the width of the column by clicking the gridline and dragging it left or right. The column headings are defined by the Datasheet Caption property. Let's change them now, with the following steps:
1. Select the CategoryCode column and then, in the Property Sheet, select the Other tab.
2. The default value of the Datasheet Caption property is blank. When this is the case, the heading text is defined by the Control Source property, which is the associated column of the underlying table. The Caption that was defined for that column will be used as the column heading. Enter Code for the Datasheet Caption property.
3. Likewise, for the CategoryDescription column, enter Description for the Datasheet Caption property.
NOTE
The configuration of the Datasheet View is independent of the Form View. Changing the order of the columns, the column widths, or the column headings has no effect on the layout of the Form View. If you allow both views of your form, you should check the layout of the Datasheet View and make sure it looks like you want it to.
The Layout View and Design View are used to design and modify the form. You will use these views extensively in subsequent chapters as you build custom forms. The Design View allows you configure all aspects of the form. The Layout View is an interesting and useful view. It looks like the Form view and displays the fields just like the Form View would, but it also allows you to add and rearrange controls. It is essentially a WYSIWYG editor.
Sorting the Records
To sort the records, click the dropdown icon next to the Description column and click the Sort A to Z link, as shown in Figure 6-8.
Figure 6.8. Sorting the Datasheet View
This will display the records in alphabetical order based on their descriptions. This change is reflected on both views. You can verify this by switching to the Form View; the records should be in the same order as in the Datasheet View. Go to the Design View and select the Data tab of the Property Sheet. Notice the Order By property has been populated, as shown in Figure 6-9.
Figure 6.9. The Order By property
Using Split Forms
All forms are based on these two display modes, Form View and Datasheet View. However, there are also two variations of these that are a composite of both views. The first is called Split Form, which shows both views simultaneously. The other is a Continuous Form, which combines features from both the Datasheet and Form views.
The Split Form is a really useful feature, especially for smaller tables. It enables you to see all the records in a compact Datasheet View. At the same time, a single record can be viewed and modified in the more user-friendly Form View. A good example would be for longer text fields. The Datasheet View will probably only show the first few words but the Form View can display the entire text on multiple lines, with scrollbars if necessary.
Generating the Media Form
You'll now create a Split View for the Media table with the following steps:
1. Close the Category form and any other tabs that may be open.
2. Select the Media table in the Navigation pane.
3. From the Create tab in the ribbon, click the More Forms dropdown and then click the Split Form link, as shown in Figure 6-10.
Figure 6.10. Creating a Split Form
This will generate a form based on the current table, which should look like Figure 6-11.
Figure 6.11. The initial Split Form
TIP
Creating a form using the Split Form button does not start the Form Wizard, which allows you to configure how the form will be generated. Instead it creates a form based on the currently selected table and includes all the available fields. You need to make sure you have the correct table selected in the Navigation pane before using this option. If you create a form from the wrong table, just delete it and try again.
Modifying the Form Fields
The form was generated using all the fields. You'll need to remove the MediaID field from both forms; to do so, right-click the MediaID column in the Datasheet View (the lower portion of the form) and click the Delete link. This will also remove this column from the Form View.
Just like with the previous form, you configure the layout of both the Form View and Datasheet View separately. We'll start with the Form View.
1. The label for the RenewalsAllowed field is a little long (How many times can a loan for this type of item be renewed?). To make this form look better, you'll use the Layout View to resize the controls. From the Design tab of the ribbon, click the Layout View button.
2. Select the label control for the RenewalsAllowed field, which will highlight the control with an orange border.
3. Resize this control so it is about half as wide and twice as high. The form should look like Figure 6-12.
Figure 6.12. Resizing the label control
4. Now adjust the column heading in the Datasheet. To do that, use the Property Sheet and select the RenewalsAllowed control.
5. In the Other tab, enter Renewals for the Datasheet Caption property.
6. Save the form and select the default form name Media when prompted.
Go back to the Form View to view the final version, which is shown in Figure 6-13.
Figure 6.13. The final Media form layout
TIP
If you remove a field from one of the views, it is automatically removed from both. This happened, for example, when you removed the MediaID field. Normally, both views will include the same fields. However, there are a couple of ways around this if you want to have different fields in each view. To remove a field from the Form View only, use the Property Sheet to set the Visible property to No. The field will still be included in the Datasheet View, but both the field and its associated label will be hidden from the Form View. To remove a field from only the Datasheet View, simply resize the column to a 0 width.
Figure 6-14 shows the form with OverdueFee field removed from the Form View and the RenewalsAllowed field from the Datasheet View.
Figure 6.14. Updated form with fields removed
TIP
In the Format tab of the Property Sheet, you can set the Split Form Orientation property. The default value is Datasheet On Top, but you can change this to put the Datasheet View on the top if you prefer. You can also put it on the right or left.
Using Continuous Forms
The other variation is called a Continuous Form. It's like a Datasheet View in that all records are displayed sequentially. However, each record is displayed as a collection of controls just like the Form View. It is basically the Form View, except instead of only displaying a single record, the form controls are repeated over and over again. Obviously, this won't work very well for long forms, but it can be useful for relatively short forms.
Generating the InventoryItem Form
You create a Continuous Form just like the Form View that you created at the beginning of the chapter. Then you have to the change the Default View property to Continuous Form. You'll now create a Continuous Form for the InventoryItem table following these steps:
1. From the Create tab of the ribbon, click the Form Wizard button. In the first dialog box, select the InventoryItem table and include all the available fields as shown in Figure 6-15.
Figure 6.15. Selecting the InventoryItem table
2. In the second dialog box, select the Tabular layout, as shown in Figure 6-16. The Tabular layout is the best choice if you're planning to use a Continuous Form. It was designed specifically for that purpose.
Figure 6.16. Using the Tabular layout
3. In the final dialog box, leave the default form title as InventoryItem, as shown in Figure 6-17. Select the second radio option, which is to modify the form's design. This will cause the new form to be opened in the Design View instead of the Form View.
Figure 6.17. Specifying the form title
The form should now be displayed in the Design View and look similar to Figure 6-18.
Figure 6.18. The initial form in Design View
The first thing you'll probably noticed is that the Label controls are in the Form Header rather that the Detail section. In a Continuous Form, only the Detail section is repeated for each record. The Form Header and Form Footer are only displayed once. Typically you will want to put the labels in the header to save "space" in the repeated section. This will help you keep the Detail as thin as possible. However, you don't have to do this; you can also put some or all of the labels in the Detail section if you want.
TIP
You cannot drag a control from one section of a form to another. If you want to move a label from the Form Header section to the Detail section, right-click the control and click the Cut link. Then right-click in a blank area of the Detail section and click the Paste link.
Also, the default layout has all of the fields on a single row, much like a datasheet. Again, you don't have to keep them that way; you can arrange them in any way you want to. In general, however, you will want to keep the Detail section thin, especially if you expect numerous records to be included.
USING THE MULTIPLE ITEMS TEMPLATE
Access often provides multiple ways to accomplish the same thing. Instead of using the Form Wizard, you could have selected the InventoryItem table in the Navigation pane and then click the Multiple Items button in the Create tab of the ribbon, as shown in Figure 6-19.
Figure 6.19. Using the Multiple Items button to create a form
This would create a new form that is very similar to the one created through the Form Wizard.
Designing the InventoryItem Form
It is helpful to think through how a form will be used; specifically, is it for view only or will it be used to add or update records. The first two forms that you created were designed to view, add, change, and delete records from the Category and Media tables. The InventoryItem form will be primarily used for viewing but will allow some restricted updates.
In Chapter 4, you created an append query that is used to insert a record into the InventoryItem table. This query takes a single parameter, the ItemID, and all the other fields will have default values.
You will eventually add a button to an Item form that will execute this query to add a copy of that item to the inventory. Also, the ItemID should not be editable once the record is created. Likewise, the Status field is controlled by the data macros that you implemented in Chapter 3. The only user-modifiable fields on the form are Condition and Comment.
This InventoryItem form will be used as a subform, which I will explain later in this chapter. It will be used to show the InventoryItem records for a specific item. Since the associated item is inferred by the context in which this subform is placed, you can remove the ItemID from this form. Also, the CurrentLoanID field is not necessary for this purpose. The Status field was generated as a ComboBox control to allow the desired value to be selected. Because this should not be editable, you'll want to replace this control with a TextBox control. This will display the current value without implying to the user that it can be modified.
Modifying the Form Fields
From the Design View, make the following changes:
1. Delete the ItemID ComboBox control. Notice that its associated label was not also removed. Because they are in different sections, they are not linked together. You'll need to manually delete the Label control as well.
2. Delete the Status ComboBox control (leave the label).
3. From the Design tab of the ribbon, click the TextBox button. Then click in the Detail section, holding the mouse button down and dragging it to form a rectangle in the same location and about the same size as the previous dropdown list control. In the Other tab of the Property Sheet, enter Status for the Name property. In the Data tab, select Status from the Control Source property.
4. The new Textbox control will have added an associated Label control. Delete this, as it is not needed.
5. On both the InventoryItemID and Status controls, set the Locked property to Yes. You can find this in the Data tab of the Property Sheet.
6. Change the Caption property of the InventoryItemID label to ID and set the Text Align property to Left.
7. Remove the CurrentLoanID ComboBox control as well as its associated label.
8. Rearrange the controls in both the Detail and Form Header section to remove the empty spaces.
The form in Design View should look similar to Figure 6-20.
Figure 6.20. The completed Design View
Select the Form object in the Property Sheet and then select the Format tab. Notice that the Default View property is already set to Continuous Form. This is set by the Form Wizard whenever you use the Tabular layout.
NOTE
You can change the Default View to Single Form if you want to see what the form would look like as a Single Form. This does not affect the design or layout of the form. Make sure you change it back to Continuous Form if you do change the Default View.
Save the form changes and select the Form View from the ribbon. The final form should look like Figure 6-21.
Figure 6.21. The final InventoryItem form
Understanding the Layout Options
Before I explain the final form that you'll create in this chapter, I want to review the layout options that are provided by the Form Wizard. You use the first dialog box to define the data source for the form. Each standard form uses a single table or query. You specify which one to use for the form and then decide which fields of that table or query to be included.
You use the second dialog box to indicate one of four layout options, which are:
Columnar
Tabular
Datasheet
Justified
When you select the associated radio button, the image changes to give you a visual representation of how your form will look.
Using the Tabular Layout Option
As I just demonstrated, the Tabular layout is used for generating a Continuous Form. The labels are placed in the Form Header and the data bound controls are aligned horizontally in a single row. This is presented in the Form Wizard with the graphic shown in Figure 6-22.
Figure 6.22. The Tabular layout
As I mentioned earlier, Continuous Forms don't have to follow this pattern. You can include labels in the Detail section and arrange the data bound controls in any manner you wish. Also, when you chose the Tabular layout, the Default View property of the form is set to Continuous Form.
Using the Datasheet Layout Option
Similarly, the Datasheet layout should be used when you want to create a form that is viewed in the Datasheet View. The graphic shown in Figure 6-23 portrays this. The labels are at the top, like the Tabular layout, but there are also record selectors on each row.
Figure 6.23. The Datasheet layout
When you use this option, the Default View property of the generated form is set to Datasheet View. However, you can choose to view the form using the From View. If you do, you'll notice that the form layout look just like forms generated with the Columnar layout, which I'll describe next. In fact, the Datasheet and Columnar options generate the exact same form, except that the Default View property is set to Datasheet View with the former option and Form View with the later.
Using the Columnar Layout Option
The last two layout options, Columnar and Justified are used when creating forms that will be viewed in the normal Form View. You used the Columnar layout when creating the Category form. This layout creates a label and its associated data-bound control side-by-side on the form. Each field is stacked vertically, which results in a column of labels and a column of controls. The image shown in Figure 6-24 represents this arrangement.
Figure 6.24. The Columnar layout
This graphic implies that there can be multiple pairs of columns; however, the Form Wizard only creates a single pair of columns. You can arrange this into multiple columns in the Layout View using a Layout control, which I'll explain later.
Using the Justified Layout Option
The final layout choice is called Justified. It generates a label that is directly above its associated data-bound control for each field. Instead of being stacked in columns, these pairs are arranged left-to-right like text on a page. This is demonstrated in the associated graphic shown in Figure 6-25.
Figure 6.25. The Justified layout
In some ways this may seem like a more desirable arrangement, as well as the most efficient use of space.
WARNING
Keep in mind that if you plan to use a layout control, which I'll explain next, the Justified layout is not compatible. When converting to a layout, the fields will be re-arranged using Columnar format before being added to the layout.
Using a Layout
Access provides a facility for easily arranging controls on a form that is often referred to as a layout. It is a grid that contains cells where you can insert controls. By placing labels and data-bound controls into a grid, you can easily format or resize an entire column or row and keep everything aligned properly.
You may have noticed that the controls in the Media form were placed in a grid. However, the Category and InventoryItem forms do not do this. These were generated using the Form Wizard, which does not use a layout. You'll fix that now.
1. Open the Category form using the Design View.
2. Select all the controls in the Detail section. You can do this quickly by dragging the mouse around a rectangle that includes all of the controls. Then, from the Arrange tab of the ribbon, click the Stacked button, as shown in Figure 6-26.
Figure 6.26. Using the Stacked button in the Arrange tab
3. You can switch to the Layout View and resize the grid columns, if necessary. Save the form and then switch to the Form View to see how the form looks.
4. Open the InventoryItem form in the Design View. In this form the labels are in the Form Header.
5. Select both the labels and the data-bound controls. For this form, click the Tabular button in the Arrange tab of the ribbon.
6. Switch to the Layout View and resize the columns. Notice that the labels in the Form Header are kept aligned with the associated control as you size each field. The final layout should look like Figure 6-27.
Figure 6.27. The modified InventoryItem form layout
7. Save the form changes, switch to the Form View, and verify the form layout.
Creating the Item Form
Now you'll add a form for the Item table, which will allow you to view, update, and create records in the Item table. You'll use the layout control to improve the arrangement of the controls. You will also imbed a sub form to view and update the associated InventoryItem records.
Using the Standard Form Template
Select the Item table in the Navigation pane and click the Form button in the Create tab of the ribbon as shown in Figure 6-28.
Figure 6.28. Using the Form button to create the Item form
WARNING
Just like with the Split Form link that you used earlier, it very important that you select the table before clicking the button. This does not launch a wizard where you can select the source table. With one click it will create a form based on the selected table.
This will generate a new form and display it using the Layout View that should look like Figure 6-29.
Figure 6.29. The initial Item form in the Layout View
Arranging the Form's Layout
The default layout creates a column for labels and a column for the data-bound controls. Because of this, all of the controls have the same width. This is less than optimal; however, since the ReplacementCost does not need to be as wide as the Description field, for instance. I'll show you how to rearrange these controls to achieve a better presentation.
Select the Arrange tab of the ribbon. You'll notice a number of buttons, shown in Figure 6-30, that will help you modify the form's layout.
Figure 6.30. The formatting buttons in the Arrange tab
Using the layout control and the tools in the Arrange tab, perform the following steps:
1. The first step is to resize the second column to give you some room to work with. Select one of the controls, which will draw an orange border around it. Click on the right edge of this border and drag it to the left so the column is about an inch wide. Notice that all the other controls are resized as well.
2. Click the Insert Right button in the ribbon three times to add three more columns.
3. Select the ItemID control and its associated label and delete them. Notice that the row remains but the cells are now empty. To remove the row, right-click one of the empty cells and click the Delete Row link.
4. Select the MediaID control and its associated label and drag them to the previous row, just to the right of the CategoryID control. This will leave an empty row, which you should delete.
5. In the same way, drag the LostFee control and its label to the right of the ReplacementCost control and delete the empty row.
6. Now you'll re-order the rows, for example, the Author field should come before the Title. Select the Author control. You'll want to move the entire row so click the Select Row button in the ribbon. Then click the Move Up button twice. In the same way, move the Description field to the last row.
7. The Author, Title, and Description fields will need more space as I previously noted. You can accomplish this by merging multiple cells together. Select the Author control and two empty cells to the right of it (do not include the third empty cell). Then click the Merge button. Notice that there is now an orange border around all three cells.
8. Do the same for the Title control, merging a total of three cells.
9. For the Description control, merge all four cells together.
10. You can resize some of the controls as appropriate. For example, you will probably want the Description control to be taller to allow for some longer descriptions. Because the Layout View displays the form with actual data, it's pretty easy to see how the data will fit into the controls. You can use the record navigation control at the bottom of the form to select different records to see how each will appear in the form.
11. Select one of the Label controls in the first column and then click the Select Column button in the ribbon. This will select the all of the labels in this column. In the Property Sheet, find the Text Align property in the Format tab and change its value to Right. Also select the MediaID and LostFee Label controls and change their Text Align property to Right.
The form should now look like Figure 6-31.
Figure 6.31. The re-arranged Item form
Adding a Subform
Now you'll add a subform to show the InventoryItem records for the selected Item. You will first need to provide a cell that the form can be placed in with the following steps:
1. Select the Description control and click the Insert Below button. This will create a new row of cells. Because the previous row had the last four cells merged together, the new row will be done the same way. This happens to be exactly what you want, in this scenario. If you needed individual cells, you could use the Split Horizontally button to split the merged cell.
2. Go to the Design tab of the ribbon and click the Subform button that is shown in Figure 6-32.
Figure 6.32. Selecting the Subform control
3. Select the empty cell that you just added. This will insert a Subform control in this cell and also add a Label control in the cell to the left of it, while the entire row is selected. Click some other control to unselect the row and then select the Subform control.
TIP
These instructions assume you have the control wizards turned off. If the Subform Wizard starts just cancel the dialog box. I will explain the control wizard in a later chapter.
4. In the Data tab of the Property Sheet, select Form.InventoryItem for the Source Object property.
5. In the Other tab, change the Name property to InventoryItem. You'll need to enlarge this row so the entire subform can be seen. You may also need to make this cell wider. Also select the Label control and change its Caption property to Inventory.
NOTE
The subform only shows a subset of the records in the InventoryItem table. Because of the relationship defined between the Item and InventoryItem tables, Access automatically filters the subform to records that are associated with the parent form.
6. Go to the Design View and select the InventoryItem form. In the Data tab of the Property Sheet, change the Allow Additions property to No. The user can view and modify records but they should not be allowed to add them.
TIP
When you select the Subform control, an orange border will be drawn around it. You'll need to select the actual form, not the Subform control that contains the form. If you click inside the upper-left corner of the form (inside the Subform control), the orange border will disappear and there will be a small black square in the upper left corner. This indicates that the form has been selected. Also, the Property Sheet will indicate that the selected object is a Form.
7. Save the form and enter Item as the form name when prompted. Switch to the Form View. The final form should look like Figure 6-33.
Figure 6.33. The final version of the Item form
Summary
In this chapter you created several useful forms without writing any code. The application now has forms that you'll use to view and modify categories, media types, and items. The key concepts that were covered include:
Understanding both the Form View and Datasheet View
Creating a Split Form, which combines both views
Using a Continuous Form that combines the characteristics of both views
Knowing which layout option to use in the Form Wizard
Using the Layout View, a layout control, and the Arrange tab to format a form
Embedding a subform
In the next few chapters, I'll build upon this foundation to create more complex forms that handle unique situations.
PivotTables are a great way to analyze a large amount of data. They operate on a table that contains raw data and produces a table of summary data. A PivotTable allows you to summarize information in a variety of ways. This ability to view the same data from multiple perspectives gives PivotTables their name. PivotTables were designed to rotate, or pivot, the data around a particular aspect. Consider a cylinder, for example. From the end it looks like a circle, while from the side it looks like a rectangle. A PivotTable provides graphical tools to quickly change perspectives so you can determine which views are most useful.
In this chapter I'll show you how to create a PivotTable and demonstrate many of the features in Access 2010 for using and customizing them.
Slicing, Dicing, and Drilling
In most applications, you will often have one or more large tables that you'll need to provide some way to extract information from. For example, in our Library project, the Loan table can be quite large. It has a record for each time an item is checked out. Even in a modest-sized library, you could have thousands of records created each day. You can analyze a large amount of data more effectively by breaking it down into various views that each describes a different aspect of the data.
A PivotTable starts with a value that you need to report. This could be the total sales, number of customers, or average response time. This value is expressed as a statistical computation such as Sum, Count or Average. You could provide a single value for the entire data set; however, most of the time you'll want to see that value expressed as a function of some other attribute. For example, "total sales per day" or "average response time for each type of request."
This technique is called slicing; the entire data set is cut into slices with each slice representing an attribute value such as date or request type. The value (total sales or response time) is them computed for each slice. For example, you could slice the Loan table using the media type. This would allow you to see how books or videos are moving. Likewise you could slice the table by the category to see whether Children's or Classics were more popular.
You'll often need to slice the data in multiple ways simultaneously. Suppose you wanted to determine the most popular media type for each category. This is what it means to dice the data. (When you cut a tomato in one direction you get slices, but when you cut it in multiple directions you get diced tomatoes.) The value is then computed for each combination of attribute values, such as Seasonal DVDs or Children's Books.
If you were to graph these values, you might display the media type on the X-axis and the category on the Y-axis. Because of this, each attribute that is used to dissect the data is sometimes referred to as a dimension. A dimension is an aspect that you'll use to slice the data. Media types and categories are good candidates for dimensions. With a PivotTable, you can dice your data in a theoretically unlimited number of directions; however, it is difficult to present more than two or three dimensions at a time. As you'll see in this chapter, Access 2010 has some creative ways to visually present multiple dimensions.
Often you need to drill into your data, which is to increasingly narrow your focus. Let's say, for example, that want to see when your busiest times are. You can start by finding the busiest days and then drill down by hours to determine more specific time periods. Drilling is analogous to taking a single slice and cutting it into smaller pieces. You can take one of the smaller pieces and further dissect it, if necessary.
NOTE
PivotTables are provided in other tools, such as SQL Server Reporting Services (SSRS) and Excel. The concepts are similar in each tool, but there are differences in their implementation and the specific features that are provided.
Creating a PivotTable View
A PivotTable is not actually a table, but a specialized view of a table. You've already used the Datasheet View, Design View, and SQL View. In Access, you'll use the PivotTable View to display an existing table. As I explained in Chapter 4, you can also use a select query just like a table, so you can use the PivotTable View on a query as well.
There are two important implications to this approach.
First, all the columns that you want included in the PivotTable must be in a single table or query. If you've done a proper normalized table design (see Chapter 2), your tables will not likely work too well for this purpose. Instead, you will probably need to create a query that joins all the necessary tables, as explained in Chapter 4.
Second, Access does not actually store the summarized data that is displayed in a PivotTable. When you save a PivotTable View, Access saves the view's configuration, not the data. You may notice a pause when you open a PivotTable View, because Access has to load the entire table or query and re-calculate the summary information. Also, you cannot edit the summarized data. When data in the underlying table is changed, the PivotTable View is not automatically updated, but it can be easily re-summarized. I'll show you how to do that later in the chapter.
Also in this chapter, you'll use the AllLoans query that you created in Chapter 4. It provides all the loan details that you'll need for your PivotTable. So let's open the PivotTable View.
1. Open the AllLoans query, which should open in the default Datasheet View.
2. Click the dropdown button under the View button in the ribbon. This will display all the available views.
3. Select the PivotTable View link, as shown in Figure 5-1.
Figure 5.1. Selecting the PivotTable View
You can also click the PivotTable View button in the lower-right corner of the application, as shown in Figure 5-2.
Figure 5.2. Selecting the available views
Understanding the PivotTable Layout
The initial blank PivotTable is shown in Figure 5-3. To design it, you'll add fields from the underlying table – in this case, the AllLoans query – to one of four areas.
Figure 5.3. The initial blank PivotTable
The large section in the center is where you'll place the value(s) that will be summarized. This is referred to as the Data area. Each record from the AllLoans query will fall into one of the cells in this area. Typically, each cell represents an aggregate function of the records assigned to that cell. For example, the cell could be the sum of OverdueFee, the count of LoanID, or the maximum DaysOverdue. In this area, you'll specify the field and the aggregate function such as sum, count, and max, to be applied to that field. The fields that are placed in this section are called Totals fields, because they are often sums or counts. The more generic name Detail field is also used, because not all aggregate functions produce totals.
The areas above and to the left of the main section contain the Column and Row fields, which are used to specify which cell each record is assigned to. For example, the Column field could be CategoryDescription and the Row field could be CheckedOut. Access finds the appropriate cell for each record by looking up its values for these fields. Each cell represents a unique combination of category and the date the item was checked out.
The section at the very top contains Filter fields. As I mentioned, you can only effectively display two dimensions at a time. The Filter field provides a third dimension, but does so by limiting the view based on the value of that field. For example, if you used MediaDescription, you could analyze one media type at a time. You could also select any subset of media types, including all of them.
You should also see a PivotTable Field List window, which is shown in Figure 5-4. This shows you all the columns in the AllLoans query. If this window is not currently visible, click the Field List button in the Design ribbon.
Figure 5.4. The PivotTable Field List window
To design the PivotTable, simply drag the desired columns from this window to the appropriate areas on the PivotTable View.
Using the PivotTable Fields
This PivotTable will be used to analyze what items are being checked out, so the first thing you'll specify is the data values that will provide that information. This will define what the view is summarizing. In this case, you'll present the number of items that have been loaned out. A Loan record is created each time an item is borrowed, so you'll simply count the number of records. To do that, use the LoanID field and the Count function, as follows.
1. Drag the LoanID field to the data area. The view will look similar to Figure 5-5.
Figure 5.5. Adding the LoanID field
2. By default, the PivotTable View shows the details by listing all the records that are represented in each cell. Because no aggregate function has been specified yet, the data area will show no totals. Select the LoanID field in the data area and then click the AutoCalc button in the Design ribbon and select the Count link as shown in Figure 5-6.
Figure 5.6. Selecting the Count aggregate function
3. This will add a row to the data area that represents the Count of loans. Click the Hide Details button in the ribbon to suppress the details. The PivotTable View will now show only the total number of loans, as shown in Figure 5-7.
Figure 5.7. The total loan count
4. In my database, I have loaned out a total of 11 books. The column heading "Count of LoanID," while accurate, is probably not the best presentation. To change this, right-click this cell in the data area and select the Properties link. Select the Captions tab and enter Loans in the Caption field, as shown in Figure 5-8.
Figure 5.8. Changing the field caption
Notice that you can also adjust other properties such as the font through the Properties dialog box.
Defining the Column Field
Now you're ready to define the dimensions that will be used to evaluate the number of loans. Start by dragging the CategoryDescription field to the Column section. The PivotTable View will now include a column for each unique value of CategoryDescription. Notice that there is now a column for Childrens, Classics, Reference, and Seasonal, as shown in Figure 5-9. There is also a Grand Total column that presents the overall total from all categories.
Figure 5.9. Adding the CategoryDescription to the Column area
NOTE
The PivotTable View does not necessarily list every category defined in the Category table; it only displays the categories that are represented in the AllLoans query. For example, I also have a Fiction category that is not shown, because no item from this category has been loaned yet.
There is a small "+" and "-" button under each column header. You can use these to show the details for each cell. In my database, there are three loans for items in the Reference category. By clicking the "+" button, you can see that LoanID 9, 11, and 12 were for Reference items, as shown in Figure 5-10.
Figure 5.10. Displaying the details of a cell
Using Date Fields
The CheckedOut field records the date/time when the item was checked out. Drag this to the Row section. Because you're the only user, no two items can currently be checked out at exactly the same time. Because each Loan record will have a different value in the CheckedOut column, you'll end up with a row for each item, as shown in Figure 5-11.
Figure 5.11. Adding the CheckedOut field
Date fields are actually a composite of several values, such as year, month, day, and so on. This makes them very useful in a PivotTable View. Notice in the PivotTable Field List window, that the CheckedOut By Month and CheckedOut By Week fields are included in addition to the CheckedOut field. Similar fields were also added for the DueDate field.
Remove the CheckedOut field from the PivotTable View by right-clicking on the field and selecting the Remove link. You can also select the field and click the Remove Field button in the Design tab of the ribbon. Drag the CheckedOut By Month field to the Row section. Expand the Years, Quarters, Months, and Days columns to drill into the data, as shown in Figure 5-12.
Figure 5.12. Drilling into the date properties
This is an ideal example of drilling into your data. Starting with the total for the year, you can then expand a specific quarter and month to see the daily totals. The "+" and "-" button on each summary row allow you to expand or collapsed that particular value.
Notice the dropdown indicator next to the Years column label. If you click this you'll see the dialog box shown in Figure 5-13. This allows you to select the years that you want included in this view.
Figure 5.13. Selecting the years to include in the view
This is only allowed at the top level of the date hierarchy. The same dropdown button is displayed next to the CategoryDescription column. In the same way you can choose to limit the categories that will be included in the view.
TIP
The CheckedOut By Week field works just like CheckedOut By Month, except the hierarchy is presented as Year, Week, and Day of Week. Months do not divide into weeks very well. This field divides each year into 52 weeks, but does not group by quarter or month. (Every 5 or 6 years has 53 weeks.) Likewise, the CheckedOut By Month field divides a year into quarters and months, but does not group into weeks. Both will present totals by year and day. You'll need to use one or the other depending on whether the quarter/month or week grouping is more useful for your analysis.
Adding a Filter Field
Drag the MediaDescription field to the Filter area. The Filter area is used to limit the records that are represented in the view. By default, the All value is selected, so this doesn't affect the results in the PivotTable View. Click the dropdown button, which will list the values that can be selected as shown in Figure 5-14.
Figure 5.14. Selecting the filter values
Unselect the All option and then select DVD video. The PivotTable View should look like Figure 5-15.
Figure 5.15. Restricting the view to only include DVD Video items
If you select more than one value for the filter, under the MediaDescription column heading it would display "(Multiple Items)."
Refreshing the PivotTable
As I mentioned earlier, a PivotTable is a summarized snapshot of your underlying table. It is not automatically updated as data is added or modified. This is done primarily for performance reasons. The summary data is recalculated when the PivotTable view is opened. Usually, when doing data analysis, you're not concerned with real-time, up-to-the-minute data, so this is a reasonable compromise.
If you need to re-calculate the data PivotTable based on the current data, just click the Refresh Pivot button on the Design tab of the ribbon, as shown in Figure 5-16.
Figure 5.16. Using the Refresh Pivot feature
Adding Other Fields
So far, I've covered the basic usage of a PivotTable View. You specify a field that contains the data values (count of LoanID in this case) and then add fields to the Column, Row, and Filter sections that allow you summarize the data using these attributes. Now I'll show you how to extend this functionality.
Including Multiple Values
The view currently has a single data point that represents the number of loans. You can add additional data points. Suppose you also wanted to know how much was charged in overdue fees. To do this, simply add the OverdueFee field to the Data area.
1. In the PivotTable Field List window, select the OverdueFee field and then select the Data Area, as shown in Figure 5-17.
Figure 5.17. Adding OverdueFee to the Data area
2. Click the Add to button to add this to the PivotTable View. The Sum function is used by default, which is correct for this field, because you want the total amount of overdue fees that were charged. However, the caption is somewhat wordy.
3. Right-click this new column in the Data area and change the caption to Fees as shown in Figure 5-18.
Figure 5.18. Changing the field caption
The modified PivotTable View should look like Figure 5-19.
Figure 5.19. The PivotTable View with a second data field
Using Calculated Columns
Now you'll add one more data value, which is the number of items that were (or are) overdue. The current AllLoans query does not have a field with that information, however. So you'll need to first modify the query and add a calculated column.
Open the AllLoans query and switch to the Design View by using the View button in the Design ribbon. Scroll to the first empty column in the lower pane of the Design View. Right-click in the Field row and select the Build link. Enter the following formula:
Overdue: −1*((Not IsNull([CheckedIn]) And [CheckedIn]>[DueDate]+1) Or (IsNull([CheckedIn])
And [DueDate]<Now()-1))
An item is considered overdue if it was checked in after the due date or if it is not yet checked in but the due date is in the past. The formula uses the IsNull function to determine if the item has been checked in. Because the item is not considered overdue until midnight on the due date, the formula must add a day to the due date. (The DueDate field does not contain a time portion; see Chapter 3 for details.)
In Visual Basic, a Boolean field has a numeric value of 0 when false, and −1 when true. To account for this, the formula multiples the result by −1. So every record will have a value of 1 if overdue and 0 if not. The PivotTable View can simply sum this new column to get a count of overdue items.
Switch to the Datasheet View and verify the values of this column are correct for each record. Then go back to the PivotTable View and you should see the new Overdue field in the PivotTable Field List window. Add this field to the Data area just like you did with the OverdueFee field. Change the caption of this column to Overdue. The PivotTable View should look like Figure 5-20.
Figure 5.20. The PivotTable View with a third data field
Adding Additional Column Fields
In the same way, you can also add additional columns to either the Column or Row area. For example, instead of using the MediaDescription field in the Filter area, you can move it to the Column area. Select the MediaDescription column in the Filter area and drag it over the PivotTable View. Notice that a blue marker will appear when the mouse pointer is over an area that will accept this field. Drag it to the right of the CategoryDescription column, as shown in Figure 5-21.
Figure 5.21. Dragging the MediaDescription field to the Column area
Now, instead of only displaying a single media type at a time, the MediaDescription field used is a sub-grouping under the CategoryDescription field, as demonstrated in Figure 5-22.
Figure 5.22. MediaDescription as a sub-grouping under CategoryDescription
For the Classics category, the PivotTable View now shows the total loans, fees, and overdue items for each media type. It also displays a total across all media types. You can also collapse each of the categories to only display the total, as shown in Figure 5-23.
Figure 5.23. Collapsing each of the categories
This hierarchy of attributes functions much like the CheckedOut By Month field that you added to the Row area. You can collapse or expand specific values to drill down into the data. Using this approach, you can add any number of fields to the Column or Row areas
TIP
You added the MediaDescription field to the right of the CategoryDescription field. This caused the media type to be a secondary level under category. If you had dragged it to the left the CategoryDescription, then the media type would be the top-level in the hierarchy.
Creating a Field Hierarchy
When planning these multi-level groupings, you should try to use attributes that are logically related. The date field is the perfect example. Months and weeks are logical subdivisions of a year as are days, hours and minutes. So it is logical to drill down from year to month to day. However, categories and media types are orthogonal attributes.
When using multiple fields in a Column or Row area you have to specify the order in which the fields are used in the hierarchy. In this case the category was first so you can see how a summary for each category and then drill down to see what media type were loaned from each category. By placing media type as a sub-group under category, you cannot view a media type across all categories.
Someone may prefer to see how a specific media type was represented in each category. You cannot accomplish both at the same time. When you have a situation like this where you need to summarize in both directions, then you should put one attribute in the Row area and the other in the Column area.
A better design would be to move the CheckedOut By Month field to the Filter area and move the MediaDescription field to the Row area. Make those adjustments in your PivotTable View and it should look like Figure 5-24.
Figure 5.24. Rearranging the fields in the PivotTable View
Notice that you can simultaneously see both category and media type summaries, as well as how the other attribute was represented in the total. Click the dropdown button next to the CheckedOut By Month field in the Filter area. The dialog box shown in Figure 5-25 will appear which you can use to filter the view to include only specific months or days, for example.
Figure 5.25. Selecting the dates to include
Remember, the Filter field(s) define the set of records that are included in the PivotTable View. By placing the CheckedOut By Month field here, you can control the time period that you want to analyze. Date fields are often a good candidate for a Filter field. However, if you want to see an attribute tracked over time, you will generally use the appropriate date field in Row area.
In the current database schema, there is a single-level categorization of items. For a large library you would probably want multiple levels, such as the Dewey Decimal system. The Dewey Decimal system defines three main levels of organization, which are referred to as classes, divisions, and sections. It is called a decimal system because there are 10 classes that each has 10 divisions, which, in turn, has 10 sections each. Thus there are 100 divisions and 1000 sections.
If you were to implement a structure such as this, then this would be an excellent candidate for using a multi-level field hierarchy in your PivotTable View. Look for other fields that logically fit into a hierarchy. The Author and Title fields are another good example.
Using the PivotChart View
Once you have defined a PivotTable View, you can easily turn this into a graphical presentation. Select the PivotChart View button from the lower right corner or select the PivotChart View link from the View button in the ribbon. The default settings will probably look something like Figure 5-26.
Figure 5.26. The initial PivotChart View
Configuring a PivotChart View
This is a fairly complex PivotTable with several values to chart. Each value is displayed as a bar in this bar graph. To see what each bar represents, you'll need to display the legend. Click the Legend button in the Design tab of the ribbon. The legend will look similar to Figure 5-27.
Figure 5.27. The PivotChart legend
The problem with this chart is that it is trying to display too many values. In a bar chart, the Column field(s) are used to define the bar legend (a color for each column) and the Row field(s) are used for the X axis. Click the "Switch Row/Column" button, which will transpose the Row and Column fields. The resulting chart should now look like Figure 5-28.
Figure 5.28. Switching the Row and Column fields
This is a little bit easier to follow. For the Classics category, for example, you can now see how each media type contributes to the summary totals Loans, Fees, and Overdue. Notice the dropdown button next to the CategoryDescription and MediaDescription fields. You can use these to filter the records that are included in the chart. Click the CategoryDescription dropdown and unselect all of the categories except Classics, as shown in Figure 5-29.
Figure 5.29. Selecting only the Classics category
Changing the Chart Type
With the data limited to a single category, you may want to use a different type of chart. Click the "Change Chart Type" button in the ribbon. Select the Line type shown in Figure 5-30.
Figure 5.30. Selecting a Line chart
The resulting chart is shown in Figure 5-31.
Figure 5.31. The PivotChart View using a line chart
You can see from this chart that overdue fees have been charged for Hardback books, but no fees have been charged for DVD videos or paperback books even though there have been overdue items of these media types.
NOTE
You will likely have different values in your database. My purpose here is to give you an example of how to read the chart.
Now display the PivotTable View by selecting the PivotTable link from the View button. Notice that this view now looks very different from where you left it. This illustrates a very important point: The PivotTable View and the PivotChart View use the same Row, Column, and Filter area definitions. If you change this configuration, it is automatically changed in the other, as well. More subtle and perhaps more significant, you can only have one PivotTable/PivotChart View for each table or query.
WARNING
You can define only one PivotTable/PivotChart View for each table or query. The PivotTable and PivotChart views share the same configuration; when you change one, the other is also updated.
Exporting a PivotTable View to Excel
Access 2010 allows you to export a PivotTable or PivotChart view to Excel. This will allow you to manipulate the data and view using the Excel application. As I mentioned earlier, the concepts are similar but the implementation is different.
From the PivotTable View, click the CategoryDescription dropdown and select all categories. Save the database and then click the "Export to Excel" button in the Design tab of the ribbon. You will see a pop-up window warning you about possible compatibility issues, shown in Figure 5-32. Click the OK button.
Figure 5.32. Compatibility warning
Access will then open the Excel application and create a PivotTable worksheet, as shown in Figure 5-33. Notice the similarities to Access 2010 and the differences.
Figure 5.33. The PivotTable View in Excel
The most obvious difference is that the multi-field hierarchy is not as evident. Notice that the CheckedOut By Month field is replaced by a set of fields, one for each of the data components such as Year, Quarter and Month.
The raw data is also imported in the second worksheet, as shown in Figure 5-34.
Figure 5.34. The underlying Excel data
Excel uses the raw data that the PivotTable is based on. In order for the PivotTable to work, all of the raw data must be imported, as well. You can use this approach create a new PivotTable and/or chart in Excel using the data from Access and the PivotTable features from Excel.
WARNING
When you export data to Excel, this is a one-time operation. Subsequent changes to the data in Access are not pushed to Excel using manual or automatic means. The only way to update Excel is to re-import the data from Access.
Summary
PivotTables and charts are an excellent way to present and analyze data from your Access database. You configure a PivotTable or chart by adding one or more fields from the underlying table to each of the following areas:
Data area: This contains the values that ate presented and normally use an aggregate function to summarize the data.
Row and Column: Fields in these areas define the attributes that are used for slicing and dicing the data.
Filter: (Optional) Fields in the Filter are used to limit the data that is being presented.
If you specify multiple Row or Column fields, they are added in hierarchical fashion allowing you to drill down from one attribute to the next. The built-in By Month and By Week date fields are an excellent example of this.
The most significant limitation to keep in mind is that you can only have one PivotTable/PivotChart View for each table or query. The PivotTable and PivotChart views share the same configuration values; when you change one, both are updated. If you want to create additional PivotTables, refer to Chapter 4 for instructions on how to create a query that joins several tables.
You can also export the data and PivotTable to Excel and manipulate the presentation using the Excel tools.
Part III. Creating Forms and Reports
In Part 2, you created your database, designed the tables, and wrote data macros to implement many of the business rules. You created queries to provide de-normalized views into your data, and even designed a pivot table to analyze the data that is being collected. Although still a bare-bones solution, your database fulfils all the basics requirements. In Part 3, however, you'll put flesh on those bones and create a rich user experience.
Chapter 6 will show you how to use the built-in form templates to easily generate many of the simpler forms. Chapters 7, 8, and 9 demonstrate how to build more complex forms from scratch. This will demonstrate a lot of handy tricks for creating useful forms. Chapter 10 will show you how to create menus and navigation pages that will guide the user to the provided features. In Chapter 11, you'll focus on the visual, branding aspects of your application, including themes, graphics, and background images.
Finally, in Chapter 12, I'll show how to create reports, which are essentially forms that are designed for print output.
Chapter 6. Standard Forms
One of the really great features of Access is that it can generate forms for you based on your table design. This is another good reason for starting with a well thought out data schema. In this chapter, I'll demonstrate several common form patterns that will satisfy many of your UI requirements. These are created using standard form templates or the Form Wizard, and do not require writing any code.
This chapter will also provide a foundation that applies to both these simple forms as well as advanced custom forms. In subsequent chapters, I'll show you how to design forms yourself and use macros and VBA code to implement more complex solutions. These custom forms are based on the same general principles that I will explain in this chapter.
Creating a Single Form
We'll start by creating a simple form to display records in the Category table.
Using the Form Wizard
From the Create tab of the ribbon, click the Form Wizard button. In the first dialog box of the Form Wizard, select the Category table, and then move the CategoryCode and CategoryDescription fields to the Selected Fields list, as shown in Figure 6-1.
Figure 6.1. Selecting the record source for the form
Each form is based on a single table or query, and the first step in designing a new form is to specify which will be the source for this form. After selecting a table or query from the dropdown list, all the available fields will display. You can then select all of the fields to be included on the form or just a subset of them.
WARNING
If your form will be used to add records, you should generally use a table for the source, rather than a query. Queries usually supply only a subset of fields or rows and often use multiple tables. All of these characteristics are problematic when inserting records. That is not to say that you cannot use a query; however, if you use a query for a form that allows new records, make sure that every required field is included on the form or has a default value assigned. Otherwise, the form will not be able to save a new record.
Notice that the primary key was not included in this form; this is a common design practice. As I discussed in Chapter 2, the primary key is a surrogate key generated by the database engine to ensure uniqueness. In many cases it is not meaningful to the end user, so there is no need to display it in that scenario.
In the second dialog, you'll need to choose how you want the fields organized on the form. As shown in Figure 6-2, there are four layout options available. I will explain these choices later in this chapter. Because this form only has two fields on it, just leave the default option of Columnar and click the Next button.
Figure 6.2. Choosing the desired layout option
In the final dialog box, enter Category for the title of this form. For the radio options, select the first option, "Open the form to view or enter information," as shown in Figure 6-3.
Figure 6.3. Specifying the form name
The new form should look like Figure 6-4.
Figure 6.4. The initial Category form
In this simple form, a single record is shown. Notice the record navigator at the bottom of the form. You can use this to move to the first, previous, next, or last record in the table. The last control in this group will display a blank record for adding a new category.
Using the Available Views
Just like with tables and queries there are several views available when working with a form. To display all the view options, perform the following steps:
1. Click the Design View button in the ribbon. If the Property Sheet is not currently visible, click the Property Sheet in the Design tab of the ribbon. There is a dropdown list at the top of the Property Sheet that you can use to select the object that you want to view. You can select the Form, any of the sections such as Form Header or Detail, or one of the individual controls. Because there are a lot of properties, these are grouped into separate tabs.
2. Select the Form object and the Format tab. There are several properties that control what views are allowed for this form. The Form Wizard generates the form with the Datasheet View turned off. Change this value to Yes as shown in Figure 6-5.
Figure 6.5. Allowing the Datasheet View
Notice that the View options in the ribbon now has four options, which are shown in Figure 6-6.
Figure 6.6. The allowed views
Every form usually has two modes that it can be viewed in; Form View and Datasheet View. You've already seen the Form View. In this view, a single record is displayed and a record navigator control is used to move through the available records. Each field is represented by an appropriate data-bound control, such as Text Box, Check Box, or ComboBox depending on the type of data contained in the field. In addition, Label controls are used to annotate what each data control is for. All of these controls are arranged on the form.
Select the Datasheet View from the ribbon, which should look like Figure 6-7.
Figure 6.7. Displaying the Datasheet View
The Datasheet View of a form looks very much like the table when it is displayed in the Datasheet View. In this view, you can re-order the columns by selecting one and then dragging it to the desired position. You can change the width of the column by clicking the gridline and dragging it left or right. The column headings are defined by the Datasheet Caption property. Let's change them now, with the following steps:
1. Select the CategoryCode column and then, in the Property Sheet, select the Other tab.
2. The default value of the Datasheet Caption property is blank. When this is the case, the heading text is defined by the Control Source property, which is the associated column of the underlying table. The Caption that was defined for that column will be used as the column heading. Enter Code for the Datasheet Caption property.
3. Likewise, for the CategoryDescription column, enter Description for the Datasheet Caption property.
NOTE
The configuration of the Datasheet View is independent of the Form View. Changing the order of the columns, the column widths, or the column headings has no effect on the layout of the Form View. If you allow both views of your form, you should check the layout of the Datasheet View and make sure it looks like you want it to.
The Layout View and Design View are used to design and modify the form. You will use these views extensively in subsequent chapters as you build custom forms. The Design View allows you configure all aspects of the form. The Layout View is an interesting and useful view. It looks like the Form view and displays the fields just like the Form View would, but it also allows you to add and rearrange controls. It is essentially a WYSIWYG editor.
Sorting the Records
To sort the records, click the dropdown icon next to the Description column and click the Sort A to Z link, as shown in Figure 6-8.
Figure 6.8. Sorting the Datasheet View
This will display the records in alphabetical order based on their descriptions. This change is reflected on both views. You can verify this by switching to the Form View; the records should be in the same order as in the Datasheet View. Go to the Design View and select the Data tab of the Property Sheet. Notice the Order By property has been populated, as shown in Figure 6-9.
Figure 6.9. The Order By property
Using Split Forms
All forms are based on these two display modes, Form View and Datasheet View. However, there are also two variations of these that are a composite of both views. The first is called Split Form, which shows both views simultaneously. The other is a Continuous Form, which combines features from both the Datasheet and Form views.
The Split Form is a really useful feature, especially for smaller tables. It enables you to see all the records in a compact Datasheet View. At the same time, a single record can be viewed and modified in the more user-friendly Form View. A good example would be for longer text fields. The Datasheet View will probably only show the first few words but the Form View can display the entire text on multiple lines, with scrollbars if necessary.
Generating the Media Form
You'll now create a Split View for the Media table with the following steps:
1. Close the Category form and any other tabs that may be open.
2. Select the Media table in the Navigation pane.
3. From the Create tab in the ribbon, click the More Forms dropdown and then click the Split Form link, as shown in Figure 6-10.
Figure 6.10. Creating a Split Form
This will generate a form based on the current table, which should look like Figure 6-11.
Figure 6.11. The initial Split Form
TIP
Creating a form using the Split Form button does not start the Form Wizard, which allows you to configure how the form will be generated. Instead it creates a form based on the currently selected table and includes all the available fields. You need to make sure you have the correct table selected in the Navigation pane before using this option. If you create a form from the wrong table, just delete it and try again.
Modifying the Form Fields
The form was generated using all the fields. You'll need to remove the MediaID field from both forms; to do so, right-click the MediaID column in the Datasheet View (the lower portion of the form) and click the Delete link. This will also remove this column from the Form View.
Just like with the previous form, you configure the layout of both the Form View and Datasheet View separately. We'll start with the Form View.
1. The label for the RenewalsAllowed field is a little long (How many times can a loan for this type of item be renewed?). To make this form look better, you'll use the Layout View to resize the controls. From the Design tab of the ribbon, click the Layout View button.
2. Select the label control for the RenewalsAllowed field, which will highlight the control with an orange border.
3. Resize this control so it is about half as wide and twice as high. The form should look like Figure 6-12.
Figure 6.12. Resizing the label control
4. Now adjust the column heading in the Datasheet. To do that, use the Property Sheet and select the RenewalsAllowed control.
5. In the Other tab, enter Renewals for the Datasheet Caption property.
6. Save the form and select the default form name Media when prompted.
Go back to the Form View to view the final version, which is shown in Figure 6-13.
Figure 6.13. The final Media form layout
TIP
If you remove a field from one of the views, it is automatically removed from both. This happened, for example, when you removed the MediaID field. Normally, both views will include the same fields. However, there are a couple of ways around this if you want to have different fields in each view. To remove a field from the Form View only, use the Property Sheet to set the Visible property to No. The field will still be included in the Datasheet View, but both the field and its associated label will be hidden from the Form View. To remove a field from only the Datasheet View, simply resize the column to a 0 width.
Figure 6-14 shows the form with OverdueFee field removed from the Form View and the RenewalsAllowed field from the Datasheet View.
Figure 6.14. Updated form with fields removed
TIP
In the Format tab of the Property Sheet, you can set the Split Form Orientation property. The default value is Datasheet On Top, but you can change this to put the Datasheet View on the top if you prefer. You can also put it on the right or left.
Using Continuous Forms
The other variation is called a Continuous Form. It's like a Datasheet View in that all records are displayed sequentially. However, each record is displayed as a collection of controls just like the Form View. It is basically the Form View, except instead of only displaying a single record, the form controls are repeated over and over again. Obviously, this won't work very well for long forms, but it can be useful for relatively short forms.
Generating the InventoryItem Form
You create a Continuous Form just like the Form View that you created at the beginning of the chapter. Then you have to the change the Default View property to Continuous Form. You'll now create a Continuous Form for the InventoryItem table following these steps:
1. From the Create tab of the ribbon, click the Form Wizard button. In the first dialog box, select the InventoryItem table and include all the available fields as shown in Figure 6-15.
Figure 6.15. Selecting the InventoryItem table
2. In the second dialog box, select the Tabular layout, as shown in Figure 6-16. The Tabular layout is the best choice if you're planning to use a Continuous Form. It was designed specifically for that purpose.
Figure 6.16. Using the Tabular layout
3. In the final dialog box, leave the default form title as InventoryItem, as shown in Figure 6-17. Select the second radio option, which is to modify the form's design. This will cause the new form to be opened in the Design View instead of the Form View.
Figure 6.17. Specifying the form title
The form should now be displayed in the Design View and look similar to Figure 6-18.
Figure 6.18. The initial form in Design View
The first thing you'll probably noticed is that the Label controls are in the Form Header rather that the Detail section. In a Continuous Form, only the Detail section is repeated for each record. The Form Header and Form Footer are only displayed once. Typically you will want to put the labels in the header to save "space" in the repeated section. This will help you keep the Detail as thin as possible. However, you don't have to do this; you can also put some or all of the labels in the Detail section if you want.
TIP
You cannot drag a control from one section of a form to another. If you want to move a label from the Form Header section to the Detail section, right-click the control and click the Cut link. Then right-click in a blank area of the Detail section and click the Paste link.
Also, the default layout has all of the fields on a single row, much like a datasheet. Again, you don't have to keep them that way; you can arrange them in any way you want to. In general, however, you will want to keep the Detail section thin, especially if you expect numerous records to be included.
USING THE MULTIPLE ITEMS TEMPLATE
Access often provides multiple ways to accomplish the same thing. Instead of using the Form Wizard, you could have selected the InventoryItem table in the Navigation pane and then click the Multiple Items button in the Create tab of the ribbon, as shown in Figure 6-19.
Figure 6.19. Using the Multiple Items button to create a form
This would create a new form that is very similar to the one created through the Form Wizard.
Designing the InventoryItem Form
It is helpful to think through how a form will be used; specifically, is it for view only or will it be used to add or update records. The first two forms that you created were designed to view, add, change, and delete records from the Category and Media tables. The InventoryItem form will be primarily used for viewing but will allow some restricted updates.
In Chapter 4, you created an append query that is used to insert a record into the InventoryItem table. This query takes a single parameter, the ItemID, and all the other fields will have default values.
You will eventually add a button to an Item form that will execute this query to add a copy of that item to the inventory. Also, the ItemID should not be editable once the record is created. Likewise, the Status field is controlled by the data macros that you implemented in Chapter 3. The only user-modifiable fields on the form are Condition and Comment.
This InventoryItem form will be used as a subform, which I will explain later in this chapter. It will be used to show the InventoryItem records for a specific item. Since the associated item is inferred by the context in which this subform is placed, you can remove the ItemID from this form. Also, the CurrentLoanID field is not necessary for this purpose. The Status field was generated as a ComboBox control to allow the desired value to be selected. Because this should not be editable, you'll want to replace this control with a TextBox control. This will display the current value without implying to the user that it can be modified.
Modifying the Form Fields
From the Design View, make the following changes:
1. Delete the ItemID ComboBox control. Notice that its associated label was not also removed. Because they are in different sections, they are not linked together. You'll need to manually delete the Label control as well.
2. Delete the Status ComboBox control (leave the label).
3. From the Design tab of the ribbon, click the TextBox button. Then click in the Detail section, holding the mouse button down and dragging it to form a rectangle in the same location and about the same size as the previous dropdown list control. In the Other tab of the Property Sheet, enter Status for the Name property. In the Data tab, select Status from the Control Source property.
4. The new Textbox control will have added an associated Label control. Delete this, as it is not needed.
5. On both the InventoryItemID and Status controls, set the Locked property to Yes. You can find this in the Data tab of the Property Sheet.
6. Change the Caption property of the InventoryItemID label to ID and set the Text Align property to Left.
7. Remove the CurrentLoanID ComboBox control as well as its associated label.
8. Rearrange the controls in both the Detail and Form Header section to remove the empty spaces.
The form in Design View should look similar to Figure 6-20.
Figure 6.20. The completed Design View
Select the Form object in the Property Sheet and then select the Format tab. Notice that the Default View property is already set to Continuous Form. This is set by the Form Wizard whenever you use the Tabular layout.
NOTE
You can change the Default View to Single Form if you want to see what the form would look like as a Single Form. This does not affect the design or layout of the form. Make sure you change it back to Continuous Form if you do change the Default View.
Save the form changes and select the Form View from the ribbon. The final form should look like Figure 6-21.
Figure 6.21. The final InventoryItem form
Understanding the Layout Options
Before I explain the final form that you'll create in this chapter, I want to review the layout options that are provided by the Form Wizard. You use the first dialog box to define the data source for the form. Each standard form uses a single table or query. You specify which one to use for the form and then decide which fields of that table or query to be included.
You use the second dialog box to indicate one of four layout options, which are:
Columnar
Tabular
Datasheet
Justified
When you select the associated radio button, the image changes to give you a visual representation of how your form will look.
Using the Tabular Layout Option
As I just demonstrated, the Tabular layout is used for generating a Continuous Form. The labels are placed in the Form Header and the data bound controls are aligned horizontally in a single row. This is presented in the Form Wizard with the graphic shown in Figure 6-22.
Figure 6.22. The Tabular layout
As I mentioned earlier, Continuous Forms don't have to follow this pattern. You can include labels in the Detail section and arrange the data bound controls in any manner you wish. Also, when you chose the Tabular layout, the Default View property of the form is set to Continuous Form.
Using the Datasheet Layout Option
Similarly, the Datasheet layout should be used when you want to create a form that is viewed in the Datasheet View. The graphic shown in Figure 6-23 portrays this. The labels are at the top, like the Tabular layout, but there are also record selectors on each row.
Figure 6.23. The Datasheet layout
When you use this option, the Default View property of the generated form is set to Datasheet View. However, you can choose to view the form using the From View. If you do, you'll notice that the form layout look just like forms generated with the Columnar layout, which I'll describe next. In fact, the Datasheet and Columnar options generate the exact same form, except that the Default View property is set to Datasheet View with the former option and Form View with the later.
Using the Columnar Layout Option
The last two layout options, Columnar and Justified are used when creating forms that will be viewed in the normal Form View. You used the Columnar layout when creating the Category form. This layout creates a label and its associated data-bound control side-by-side on the form. Each field is stacked vertically, which results in a column of labels and a column of controls. The image shown in Figure 6-24 represents this arrangement.
Figure 6.24. The Columnar layout
This graphic implies that there can be multiple pairs of columns; however, the Form Wizard only creates a single pair of columns. You can arrange this into multiple columns in the Layout View using a Layout control, which I'll explain later.
Using the Justified Layout Option
The final layout choice is called Justified. It generates a label that is directly above its associated data-bound control for each field. Instead of being stacked in columns, these pairs are arranged left-to-right like text on a page. This is demonstrated in the associated graphic shown in Figure 6-25.
Figure 6.25. The Justified layout
In some ways this may seem like a more desirable arrangement, as well as the most efficient use of space.
WARNING
Keep in mind that if you plan to use a layout control, which I'll explain next, the Justified layout is not compatible. When converting to a layout, the fields will be re-arranged using Columnar format before being added to the layout.
Using a Layout
Access provides a facility for easily arranging controls on a form that is often referred to as a layout. It is a grid that contains cells where you can insert controls. By placing labels and data-bound controls into a grid, you can easily format or resize an entire column or row and keep everything aligned properly.
You may have noticed that the controls in the Media form were placed in a grid. However, the Category and InventoryItem forms do not do this. These were generated using the Form Wizard, which does not use a layout. You'll fix that now.
1. Open the Category form using the Design View.
2. Select all the controls in the Detail section. You can do this quickly by dragging the mouse around a rectangle that includes all of the controls. Then, from the Arrange tab of the ribbon, click the Stacked button, as shown in Figure 6-26.
Figure 6.26. Using the Stacked button in the Arrange tab
3. You can switch to the Layout View and resize the grid columns, if necessary. Save the form and then switch to the Form View to see how the form looks.
4. Open the InventoryItem form in the Design View. In this form the labels are in the Form Header.
5. Select both the labels and the data-bound controls. For this form, click the Tabular button in the Arrange tab of the ribbon.
6. Switch to the Layout View and resize the columns. Notice that the labels in the Form Header are kept aligned with the associated control as you size each field. The final layout should look like Figure 6-27.
Figure 6.27. The modified InventoryItem form layout
7. Save the form changes, switch to the Form View, and verify the form layout.
Creating the Item Form
Now you'll add a form for the Item table, which will allow you to view, update, and create records in the Item table. You'll use the layout control to improve the arrangement of the controls. You will also imbed a sub form to view and update the associated InventoryItem records.
Using the Standard Form Template
Select the Item table in the Navigation pane and click the Form button in the Create tab of the ribbon as shown in Figure 6-28.
Figure 6.28. Using the Form button to create the Item form
WARNING
Just like with the Split Form link that you used earlier, it very important that you select the table before clicking the button. This does not launch a wizard where you can select the source table. With one click it will create a form based on the selected table.
This will generate a new form and display it using the Layout View that should look like Figure 6-29.
Figure 6.29. The initial Item form in the Layout View
Arranging the Form's Layout
The default layout creates a column for labels and a column for the data-bound controls. Because of this, all of the controls have the same width. This is less than optimal; however, since the ReplacementCost does not need to be as wide as the Description field, for instance. I'll show you how to rearrange these controls to achieve a better presentation.
Select the Arrange tab of the ribbon. You'll notice a number of buttons, shown in Figure 6-30, that will help you modify the form's layout.
Figure 6.30. The formatting buttons in the Arrange tab
Using the layout control and the tools in the Arrange tab, perform the following steps:
1. The first step is to resize the second column to give you some room to work with. Select one of the controls, which will draw an orange border around it. Click on the right edge of this border and drag it to the left so the column is about an inch wide. Notice that all the other controls are resized as well.
2. Click the Insert Right button in the ribbon three times to add three more columns.
3. Select the ItemID control and its associated label and delete them. Notice that the row remains but the cells are now empty. To remove the row, right-click one of the empty cells and click the Delete Row link.
4. Select the MediaID control and its associated label and drag them to the previous row, just to the right of the CategoryID control. This will leave an empty row, which you should delete.
5. In the same way, drag the LostFee control and its label to the right of the ReplacementCost control and delete the empty row.
6. Now you'll re-order the rows, for example, the Author field should come before the Title. Select the Author control. You'll want to move the entire row so click the Select Row button in the ribbon. Then click the Move Up button twice. In the same way, move the Description field to the last row.
7. The Author, Title, and Description fields will need more space as I previously noted. You can accomplish this by merging multiple cells together. Select the Author control and two empty cells to the right of it (do not include the third empty cell). Then click the Merge button. Notice that there is now an orange border around all three cells.
8. Do the same for the Title control, merging a total of three cells.
9. For the Description control, merge all four cells together.
10. You can resize some of the controls as appropriate. For example, you will probably want the Description control to be taller to allow for some longer descriptions. Because the Layout View displays the form with actual data, it's pretty easy to see how the data will fit into the controls. You can use the record navigation control at the bottom of the form to select different records to see how each will appear in the form.
11. Select one of the Label controls in the first column and then click the Select Column button in the ribbon. This will select the all of the labels in this column. In the Property Sheet, find the Text Align property in the Format tab and change its value to Right. Also select the MediaID and LostFee Label controls and change their Text Align property to Right.
The form should now look like Figure 6-31.
Figure 6.31. The re-arranged Item form
Adding a Subform
Now you'll add a subform to show the InventoryItem records for the selected Item. You will first need to provide a cell that the form can be placed in with the following steps:
1. Select the Description control and click the Insert Below button. This will create a new row of cells. Because the previous row had the last four cells merged together, the new row will be done the same way. This happens to be exactly what you want, in this scenario. If you needed individual cells, you could use the Split Horizontally button to split the merged cell.
2. Go to the Design tab of the ribbon and click the Subform button that is shown in Figure 6-32.
Figure 6.32. Selecting the Subform control
3. Select the empty cell that you just added. This will insert a Subform control in this cell and also add a Label control in the cell to the left of it, while the entire row is selected. Click some other control to unselect the row and then select the Subform control.
TIP
These instructions assume you have the control wizards turned off. If the Subform Wizard starts just cancel the dialog box. I will explain the control wizard in a later chapter.
4. In the Data tab of the Property Sheet, select Form.InventoryItem for the Source Object property.
5. In the Other tab, change the Name property to InventoryItem. You'll need to enlarge this row so the entire subform can be seen. You may also need to make this cell wider. Also select the Label control and change its Caption property to Inventory.
NOTE
The subform only shows a subset of the records in the InventoryItem table. Because of the relationship defined between the Item and InventoryItem tables, Access automatically filters the subform to records that are associated with the parent form.
6. Go to the Design View and select the InventoryItem form. In the Data tab of the Property Sheet, change the Allow Additions property to No. The user can view and modify records but they should not be allowed to add them.
TIP
When you select the Subform control, an orange border will be drawn around it. You'll need to select the actual form, not the Subform control that contains the form. If you click inside the upper-left corner of the form (inside the Subform control), the orange border will disappear and there will be a small black square in the upper left corner. This indicates that the form has been selected. Also, the Property Sheet will indicate that the selected object is a Form.
7. Save the form and enter Item as the form name when prompted. Switch to the Form View. The final form should look like Figure 6-33.
Figure 6.33. The final version of the Item form
Summary
In this chapter you created several useful forms without writing any code. The application now has forms that you'll use to view and modify categories, media types, and items. The key concepts that were covered include:
Understanding both the Form View and Datasheet View
Creating a Split Form, which combines both views
Using a Continuous Form that combines the characteristics of both views
Knowing which layout option to use in the Form Wizard
Using the Layout View, a layout control, and the Arrange tab to format a form
Embedding a subform
In the next few chapters, I'll build upon this foundation to create more complex forms that handle unique situations.
No comments:
Post a Comment