Dear Charles:
Looking in the archives, I see this was likely the topic, "Crosstab
Row Interval." Well, no matter.
I need to know whether the query is for Jet or MSDE. If you're
writing a typical MDB then it's Jet.
In order to create what you want, please start by writing a simple
SELECT query that can show me the names of your table and columns. I
should contain the following sets of columns:
1) the columns by which you are grouping. Typically, these are the
columns that identify each group of rows and appear to the left. They
are usually in a hierarchy if there is more than one. If so, I'll
take your order to be correct.
2) the column or columns that become the column heading(s). In a
crosstab, this is just one column, but it's possible to pivot on more
than one, although displaying the results is trickier. Again, I'll
take the order you present them (if more than one) to be your required
hierarchy.
3) the column or columns that make up the intersection. By
intersection, I mean the position of data in the matrix set up by 1)
and 2) above. These can be aggregates or not. The order is not
critical here.
So, your response may look like:
<sample>
SELECT G1, G2, G3, T1, T2, T2, C1, C2, C3
FROM YourTable
G1, G2, and G3 are the grouping columns. T1, T2, and T3 are the
columns transformed into headings. C1, C2, and C3 are the cell
values, and are aggregated as MAX(C1), SUM(C2) and AVG(C3)
</sample>
Please respond something like what I've put into the sample above.
This will allow me to write some SQL that is most likely to work with
the minimum of fuss.
Now, if you want this, I'm assuming you can't use a simple crosstab
for this. There's one more issue to cover.
If the transform columns are constant, then a simple query will
suffice. If not, then the SQL will have to be created dynamically.
Of course, if there are a large number of transform "columns"
generated, and especially if there are a large number of "cell
intersection columns, you can run into the limit of 255 columns for a
query. We can handle this by "scrolling" through sets of results,
each one just large enough to fill a screen or page.
Some of the techniques necessary to do a really nice job of this are
near the limits of my capability, so I expect this may border on the
"extreme expert" realm. I know, not too humble of me, but you need to
be aware of this before you invest much of your time. Let's not leap
till we've covered all the bases. I'd like you to just concentrate on
getting me the info needed to get started.
Oh, yes, you can also filter both the rows (groups) and columns if
desired. So, pass this along, too, if necessary.
Finally, just imagine a form involving all the above. Yes, a form,
where you can actually enter data in each value at each intersection
and affect the table behind this. Not for aggregate values in the
intersections, mind you, but for "raw data" at each intersection. Not
only edit the data, but allow multiple users to edit it
simultaneously, with rules and prompts as necessary to detect and
overcome "collisions" between entries made. Also with the horizontal
scrolling that allows for unlimited width in terms of the number of
columns, and a combo box drill down to search for the column to which
you want to move.
The above is what we currently consider to be the upper limit of what
is possible. But there are numerous things you can do this way which
you cannot do using crosstabs:
1) non aggregate values at the intersection
2) multiple values at the intersection
3) multiple value columnar hierarchy
4) data entry
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
