You are here:

Using MS Access/Report data columns, but major groups not in columns


I need to build a report for an index I've created in Access 2007. Each major index field (let's call it "Category") has a random number of sub-categories (let's call these "SubCategory") between 1 and maybe 100+ assigned to it. When I generate the report, I need each "Category" to be grouped on it's own line (grouping by "Category"), but then I need the detail for each "Category" to show up in 3 columns. My underlying query would sort the records for me, so that would not necessarily need to be addressed in the report, although I could add a grouping on the "SubCategory" name.

Any ideas?

I'm thinking maybe creating a concatenated text string through VBA with "SubCategory" values having an ASCII tab in between values 1 and 2 and an ASCII Line feed after each third record, and a special character after the last "SubCategory" entry to signify the end of that group. Then, I would need to somehow display this string in the detail. The detail would then look like "SubCategory1" + <TAB> + "SubCategory2" + <TAB> +"SubCategory3" + <Line Feed> for every three records associated with each "Category".

Does this sound feasible or is there a better way to display the detail of the report in columns, but not the grouping areas?

So you want something like this:

SubCat1          SubCat2          Subcat3
Subcat4          SubCat5          Subcat6

SubCat1          SubCat2          Subcat3

Then set your Detail band to display 3 columns using across then Down. This is done in the Page Setup.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2016 All rights reserved.