I take It This is NOT At All Easy

[Ray S.]

I posted a question early this morning:

I have a query result, call it "qryTemp," that gives me three fields:
functional_area, business_unit, and amount_sum. I want to create a report
that has the functional_areas as details in a column along the left side of
the report. Along the top of the report, I want to put the business_units
(along with "functional area," as header titles. In the details of the
report, I want to display the amount_sum that corresponds to each combination
of functional_area and business_unit. Can I do this? How?

Normally, by now I have received many responses to any question I post, but
not this time. I've racked my brain trying to figure out how to do this, but
it escapes me. I can do it pretty easily in Excel, generating a pivot of my
query result, but I really don't want to have to use Excel.

Does anybody have any idea of how I can get this information from query into
report maintaining the structure I want?

 

[akphidelt]

try creating a Crosstab Query.

You would have
functional_areas as the Row Heading
business_units as the Column Heading
amount_sum as the Value (make sure you change this to Sum instead of Group By)

 

[Evi]

It's not that easy actually.
If you have Access 2007 you can (according to its boasts) create a Pivot
Table that is just like an Excel one. I
Otherwise, you are describing a Report based on a Crosstab query except
that you can only have 1 field as the column header so I suggest that you
Concatenate Business Units and Functional Area.
(To easily base a report on a crosstab query, click on the query, go to
Insert, Report, Autoreport,
Crosstab queries can be unstable; if you change the name or delete a column
header you will get a xxx Not Found message when you try to reopen the
query, so you'll need to use one of the methods available for making them
workable if that will provide you with your solution.

If you have a search for Dynamic CrosstabHeaders in Google, especially posts
by Duane Hookom, you will see what is involved.
Solutions can include giving each

Write back if this is what you want. I'd put the subject heading Dynamic
Crosstab Headers for your email because I can see that this will be your
main difficulty.

Evi

 

[Evi]

Must stop posting messages before I've finished writing them!!!
Edited below:

It's not that easy actually.
If you have Access 2007 you can (according to its boasts) create a Pivot
Table that is just like an Excel one.
Otherwise, you are describing a Report based on a Crosstab query except
that you can only have 1 field as the column header so I suggest that you
Concatenate Business Units and Functional Area.
(To easily base a report on a crosstab query, click on the query, go to
Insert, Report, Autoreport, Tabular)
Crosstab queries can be unstable; if you change the name or delete a column
header you will get a xxx Not Found message when you try to reopen the
query, so you'll need to use one of the methods available for making them
workable if that will provide you with your solution.

If you have a search for Dynamic CrosstabHeaders in Google, especially posts
by Duane Hookom, you will see what is involved.
Solutions can include giving each FunctionalArea+BusinessUnit a sequential

number in a Unique Query and then using the Column Header Property to type
in those numbers. You would then use the sequential number as the column
header (but hide those labels) and have textbox header labels with a DLookup
to find the text for that number

 

[Ray S.]

Thanks Evi. I tried the cross-tab query and it creates a beautiful report,
but I have a couple of wrenches to toss in. First, I have to add a column to
the report titled OH (for overhead), which is the result of a completely
different query. I also have to add one line to the report (one of the
functional areas) that comes from adding up the results of another couple of
different queries. Does this make the cross-tab solution unworkable? From
what I read, they are pretty unstable. I'll try to see what happens.