Multiple column headers in crosstab?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In know my share of SQL but im new to Access. I know what crosstabs are and
how to make them in Access. However, it seems only one database field can be
specified for column headers. And I'm trying to achive a result with two
groups of a headers; each "parent" header with several "child" headers if you
will. Something like this:

| Employee1 |
Employee2 | ...
PROCES | Number | TotalTime | AvgTime | Number | TotalTime | AvgTime | ..
-----------------------------------------------------------------------------------------------
proces1 | 3 | 90 | 30 | 7 | 140
| 20 |
proces2 | 5 | 50 | 10 | 11 | 121
| 11 |
.... | ... | ... | ... | ... |
... | ... |


Is het possible to have such a header config in a crosstable?
 
Dear Spaan:

For clarity, crosstabs are native to Access Jet, and not to MSDE (which is
also included with Access since 2000 and has its own "native" development
environment within Access).

As you say you are familiar with other databases, you probably know that
crosstabs are unique to Jet. Perhaps you are not aware that crosstabs are
quite possible to do in other databases. They are implemented with
subqueries.

A crosstab can have more than one level or row heading, but only one level
of column heading. Using a subquery system, you may implement this quite
readily.

In using this technique, whether for Jet of for some other database, the SQL
must be generated in code. For a Jet application, I would do this in a VBA
module. For an MSDE based application, I would do this in a Stored
Procedure.

The primary skill is to learn how to code the SQL manually. Expanding that
to the coded SQL is a different skill, demanding primarily debugging skill.

For the query you show, the SQL might look like this:

SELECT PROCES,
(SELECT Number
FROM YourTable T1
WHERE T1.PROCES = T.PROCES
AND T1.Employee = "Employee1")
AS Employee1Number,
(SELECT TotalTime
FROM YourTable T1
WHERE T1.PROCES = T.PROCES
AND T1.Employee = "Employee1")
AS Employee1TotalTime,
(SELECT AvgTime
FROM YourTable T1
WHERE T1.PROCES = T.PROCES
AND T1.Employee = "Employee1")
AS Employee1TotalTime
FROM YourTable T
ORDER BY PROCES

The inner set of column headings would seem to be set as being
Number/TotalTime/AvgTime. The outer set would be determined by a query
giving the list of Employees. For the VBA solution, this is a recordset
iteration. For the MSDE Stored Procedure, it is a cursor. The code to
generate the SQL text is pretty stright forward.

The subqueries above can easily be changed to be aggregate functions where
needed.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
 
Hi Tom,

Big thx for your quick reply. I can see the big picture in what you
explained. But to clarify one thing: in you example code you use column
headers aliases like Employee1Number etc. Thus in effect combining the outer
en inner header in one row. Am I correct to assume that it is just not
possible to visualy seperate the two header groups in two header rows? I'm
afraid not.
Well, maybe in an Access report. Gonna look into that now.

A bit off topic and fyi:
In SQL it is possible to achieve a crosstab without using a cursor. It's
done in with help of a ##temp table. If you didn't know that already and are
interested: check http://www.sqlteam.com/item.asp?ItemID=2955. I actually
used it on this same problem with the same result you discribed (with headers
like Employee1Number).

Greetings and thx again,
Spaan
 
Dear Spaan:

I think I can see your concept of "multiple rows of column headings" but I
don't see how that would be possible in a query. The column heading names I
used do combine the tiers of headings. It seems to me to be a misconception
to think of the columns headings as being a "row" in the query. Column
headings form a "row" in the report (or more than one when there is a
heirarchy of column headings) but this is not even remotely a function of
the query, but of the programmer's organization of the problem. It would be
possible to use some separator character, such as a period, to separate the
tiers of the column heading down the hierarchy. This could then be
automated in naming the tiers of columns for the report by parsing.
Instead, I have typically used the same query that generated the list of
"outer tier" sub-query filter values (Employee1, Employee2, . . .) to
generate those names as well, placing them in an array, from which the
report headings can be generated.

The application of the ##temp approach to a multiple tier of column headings
is going to be a bit tougher. Given that the number of columns is going to
be limited by the width of paper or screen, I don't really find the cursor
approach to be much of a difficulty. For any reasonable, and usable, number
of columns it's going to take only a fraction of a second. Anyway, I was
trying to provide a simple and direct approach that I know works for me.
Was it helpful for you?

A final interesting discussion is how to generate a succession of queries to
perform this. If the number of "Employees" is large, it will not fit all on
one piece of paper, or on the screen. For the screen, I generate successive
queries as the user scrolls a horizontal bar, requerying each time. This
can even be done when the form is interactive with any changed values being
stored, then a new set of columns displayed. This cannot, of course, be
done when the values are aggregate. You can also generate what is
effectively a series of reports, each report covering a new set of column
headings.

Tom Ellison
 
With your remark: -It seems to me to be a misconception to think of the
columns headings as being a "row" in the query.- , you hit the bulls-eye. I
realised the flaw in my thinking when I looked at your example. Certainly
helpful in that way :)

And that bit about the ##temp approach was not intended as a better way.
Just trying to show off to an expert ;)

Since the priority and usage of this data is fairly low, I think I'm not
going to spend much time on coding. They'll have to settle for a simpler
report then :)
 
Back
Top