Report Grouping Structure in Columnar Form

[musesshadow]

Alright, this one should be tricky. I have a report I want to run where I want output to be in a columnar form as such.


Group 1
-----Group 2
----------Group2Item Group2Item Group2Item Group2Item

Moreover, for the list of items in group two, they should be located under their respective titles for the grouping.

i.e.

Item1----Item2----Item3----Item4
Price1-------------Price3---------
Price1----Price2------------------
----------------------------Price4

Is this even possible or am I asking too much of Access?

Thanks!

 

[BNoble]

Nope your not asking too much at all

In the Report GUI, there is an icon, that brings up a sorting and grouping dialog.

You can create as many groups as you choose by whatever criterion you want sorted any way your heart desires.

In addition, for each group, it can have it's own header, or footer, at your whim, subtotals and all.....

The query that feeds that report will probably be quite a challenge however.

Given the amount of information that you gave....that's about the best I can give you.

 

[musesshadow]

It's not the grouping and the sorting that's the problem - that's pretty easy to do. It's getting the layout I want so that the Detail level of the report prints from left to right instead of top to bottom.

Let's see if I can explain this better.

I have a list of hardware items and prices for each. I want the report to have hardware grouped by department, then by person, and show how much money is being spent for new hardware, but I want it all to be in one row.

i.e.

Department: Tech Support

Employee-----Desktop-----Monitor-----Portable-----Printer-----Other
Dude------------1000-------------------------------------------200
Lady------------1000--------1500----------------------------------
Chickie-----------------------------------2000------------------500

Make more sense?

 

[Therese]

Good Question

That is a very good question. I have a similar problem, so if anyone has any suggestions PLEASE HELP. In my case, I want the report to almost mimic a spreadsheet. But the layout described by musesshadow illustrates it very well.

 

[Rich]

Use a crosstab query, in this case it would probably have to be dynamic

 

[musesshadow]

Crosstab Queries, Excellent... how's that?

Actually Therese, I'm migrating an old spreadsheet based system into Access for easier reporting, so I guess we're looking for the same thing.

So now we know we have to use [Dynamic] Crosstab Queries. Is there a good resource on how to do that? I know SQL, and I'm familiar with the Oracle, MySQL, and DB2 reporting schemes, but Access 2000 is new territory for me.

One step closer. MWA HA HA.

 

[Therese]

Thank You

Crosstab query worked GREAT! Thanks for your help. I had never done a crosstab query before. You learn something new all the time.......

 

[musesshadow]

Yeah, the crosstab definitely did the trick! Thanks a lot guys - really made my day. Access has wizards to build queries, how cool is that?!

Took me a while to figure out how to link up two crosstabs (needed two value points) but once you get it it's pretty easy.

One last question to tie everything off though, and this is only a style point. Is there a way to get the detail section to print in alternating background colour? Row 1 (grey), Row 2 (white), Row 3 (grey), etc.

 

[Rich]

Article ID: Q114086

 

[musesshadow]

KB210392 for Access 2000

Thanks again Rich for all your assistance.

 

[livvie]

Article ID: Q114086

I am using A2K .adp and I have the same problem except CrossTab queries aren't an option in .adp (unless I've missed something)