Grouping data in reports - Access 2007

[Andrew Shearer]

I have hit the 2GB limit in Excel and am now looking at a solution in Access 2007. The issue I'm facing is replicating what I'm doing in Excel into Access. The main challenge and my question is how to: in a Report display grouped data the same way as in the attachment. I want the groups text box to be displayed vertically and to grow or shrink with the number of rows returned in the report. This needs to apply to the next group level too.

Any ideas?

[Rabbit]

Access has a 2 GB file size limit as well...

[gershwyn]

First of all, using Access to duplicate exactly what you're currently doing in Excel will probably fall somewhere between impossible and incredibly frustrating. You can certainly display the information grouped the way you have it, but the visual elements you have will not be easily reproduced. You should think about what you consider important about your layout and what can be adapted to a different format that lends itself better to Access. (It may also be worth trying to figure out a way to make your Excel solution smaller so as to not lose the work you've already done.)

Disclaimer aside, I can roughly recreate what you have using subreports. The subreport should contain the fields that are displayed for each record (sku, description, etc.) Make sure the report's underlying query includes what category it belongs to - we'll need this value to link to the main report.

Make a query that uses a group by clause to list out each category to be displayed (ID and description), and create a report based off of this. Create a text box in the detail section, and set its vertical property to True. This should be bound to the category's description. Next to that place your subreport, linking to the main report on the ID of the category.

If you do decide to go down this path, that should accomplish the basis of what you need - a vertical category header that sits next to the details of the items in the category. Making it look nice is another matter altogether, which will probably involve a lot of "fun"... only in an obscure, masochistic sense of the word.

[Rabbit]

All of this is moot though. If he hit the file size limit in Excel then he's going to hit the same limit in Access. Even if he splits the database into a front end and back end and constantly compacts the database, he will eventually hit the file size limit again. The question, then, is what is the rate of growth and is that time spent developing everything in Access worth the time gained?

It may be time to look beyond Access, at least as far as using it for it's DBMS capabilities. You could still use it as a front end so if you were to develop it in Access then you would be saved the trouble of redeveloping everything once you have to move the data to a different DBMS.

[Andrew Shearer]

Thanks. I was hoping for a simpler solution, but you are probably correct in that perhaps Access is not the best solution.

[Andrew Shearer]

You're on the money Rabbit. The 2GB excel sheet has already been split due to file size. So essentially I have been playing with Access to see if I can replicate the end result, with the aim of adding the other sheets together into a single solution. I don't want to spend weeks developing this only to find I have wasted my time due to a limitation somewhere.