In this week's query, learn how to create and group a pivot table and test your Access skills.
Access developers often hide objects from users. Access even makes it easy - just-right click the object in the Navigation Pane (or Database window in Access 2003 and earlier) and choose Hide In This Group or Hide. Recently, a reader asked how to hide a query generated by VBA code. How would you do it and why might you want to?Last week we asked… How would you improve this Excel spreadsheet? When I responded to this particular reader, I suggested a pivot table and sent her an example. She'll have to generate a new pivot table as needed, but it's easier than maintaining the CountIf() table. To create a pivot table, based on the example sheet from the challenge, do the following:
- Select the data and headings. In this case, that's A1:D7.
- Click the Insert tab.
- Click PivotTable in the Tables group and click OK. Excel will generate a blank pivot table.
- From the field list to the right, drag the fields and data to the pivot table. Use the following figure as your guide.
- Right-click any date in the Date column (in the pivot table, not the original data).
- Choose Group from the resulting context menu.
- In this case, you want to group by the month and that's what Excel chooses, so click OK.