You are here:

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

Advertisement


Question
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?

Answer
So you want something like this:

Category1
SubCat1          SubCat2          Subcat3
Subcat4          SubCat5          Subcat6

Category2
SubCat1          SubCat2          Subcat3
Subcat4          


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

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

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

Experience

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

Organizations
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

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.