excel: grouping data returned from MDB query

[dh42891]

I'm trying automatically generate a report I have to reproduce for a large volume of data. I have the MDB query set up returning the correct values, but I need the data grouped- similar to how, say, Crystal Reports can group based on an attribute. Is there a way to do this? Is there a reporting wizard or tool I can look into? I've not tried to do anything like this in Excel before so I'm just scratching at the surface. Thanks,

dylan

 

[SkipVought]

Hi,

Take a look at the PivotTable Wizard -- Data/PivotTable & PivotChart Rport...

There's also the Subtotal feature -- Data/Subtotal... which does grouping based on the SORT/grouping of your table.

There's also Group & Outline -- Data/Group & Outline...

You can also Query your Table -- Data/Get External Data/New Database Query -- Excel Files -- YOUR WORKBOOK...

Lots of reporting tools.

Skip,

_[red][/red]

 

[dh42891]

Dang I was hoping there was just one answer to look into! Thanks a lot, that's what I was looking for and gives me plenty to start with.

dylan

 

[xlbo]

Any reason for not grouping in the query? or do you also need to see the detail ?

I would suggest starting having a look at Pivot Tables - they are a specific aggregation tool for de-normalised data

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[dh42891]

I have the query sorting the data first by the StreamName (which is what I need it grouped by) and then by a number field.

I'm not sure what you're referring to as the 'detail.' I do need to see all the detail for each record, but I need a group (and a group header) for each StreamName. Also, as a side note, all this must look exactly like a hardcopy template I was given.

The Pivot Table looks like the most-likely candidate, but also the most complicated. It seems like I can define a worksheet as the input for the Pivot Table, and create a template pivot table. That way I can just change the data I bring into the 'data sheet' and let the pre-formatted pivot table group and present the report. Am I on the right track?

dylan

 

[xlbo]

absolutely spot on

you can also change the pivot table layout whenever you want and if you wanna get really clever, you can create a dynamic named range which sits over the data and then base the pivot table on that. This can be really useful if your data is liekly to change size (no. of rows)



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question