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:

  1. Select the data and headings. In this case, that’s A1:D7.
  2. Click the Insert tab.
  3. Click PivotTable in the Tables group and click OK. Excel will generate a blank pivot table.
  4. From the field list to the right, drag the fields and data to the pivot table. Use the following figure as your guide.


At this point, the pivot table isn’t summarizing the titles by month though. You could create a Month column in the original data, but that isn’t necessary—and you’ll probably be surprised just how easy it is to group those dates! Here’s how:

  1. Right-click any date in the Date column (in the pivot table, not the original data).
  2. Choose Group from the resulting context menu.
  3. In this case, you want to group by the month and that’s what Excel chooses, so click OK.



Robert was the first to suggest a pivot table, as did many of you. Some of you mentioned advanced CountIf() functions and even the SumProduct() and Offset() functions. Those are interesting ideas, but the pivot table is quick and easy! I like Jkiernan’s idea of a dynamic range and a pivot table—I haven’t tried it yet, but it sounds intriguing!
Thanks for another interesting challenge. If any of you have an Office document that you’d like some help with, just let me know. I’d be glad to use it in a challenge.