By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,992 Members | 967 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,992 IT Pros & Developers. It's quick & easy.

Grouping data in reports - Access 2007

P: 3
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?
Attached Images
File Type: jpg ss.jpg (145.2 KB, 152 views)
Dec 22 '10 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,357
Access has a 2 GB file size limit as well...
Dec 22 '10 #2

100+
P: 122
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.
Dec 22 '10 #3

Rabbit
Expert Mod 10K+
P: 12,357
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.
Dec 22 '10 #4

P: 3
Thanks. I was hoping for a simpler solution, but you are probably correct in that perhaps Access is not the best solution.
Dec 22 '10 #5

P: 3
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.
Dec 22 '10 #6

Post your reply

Sign in to post your reply or Sign up for a free account.