Dear CS:
What you are proposing could certainly be done. I would envision this as a
cross-tab query as the RowSource for a Form. The Click Event of a selected
"Cell" could bring up the detail of the "intersection" (the selected row and
column headings for that "cell" in the crosstab generated grid) in a
subform.
I have built crosstab based forms. This is the real trick. Such a form may
have a variable number of columns, and variable column heading names. They
are bound to columns in the query at run-time, since the names of the
columns and the number of columns are not known when it is being designed.
I have even built such crosstab forms where the horizontal scroll bar
triggers a new query for the new set of columns. In this way, you can
handle more than 255 columns of data by filtering it down to a smaller
number and requerying if the user scrolls the form.
To do this, the variable column heading names and the number of such columns
is first queried, and the column names are placed in an array. Using these
as "keys" to the set of columns to be queried, a range is established on
which successive crosstab "segments" are queried based on the movements of
the horizontal scroll bar.
This is a fairly expert technique, and would be a pretty hefty undertaking
if you are not accustomed to using such features. On the other hand, it is
a very powerful and rewarding technique, and is probably the most intuitive
way to handle it. While it is complex to build, users need no explanation
of what they're seeing.
Then, there is the crosstab form in which you can enter new data in the
cells. This requires that the intersections be not actual aggregates, but
be single entries as in a many-to-many relationship. The trick here is to
generate a temporary table for the intersection data, edit that, then
translate the edited temporary table back into the real data when the form
closes.
I would classify this one as an "extreme expert technique" but it is again
very rewarding. The 255 column limit can again be overcome, this time by
re-running the whole crosstab to intermediate table query whenever the form
is horizontally scrolled. You must also save any data changes to any
"cells" that scroll off the screen.
A final version is where the intersections for the crosstab are not single
values, but a set of related values. This requires creating the crosstab
query effect without actually using a crosstab query. Everything that can
be done in a crosstab can be done without a crosstab. You would need to
query first to find the set of columns needed, then generate aggregate
subqueries to create each column of intersections. This can then be used to
generate a crosstab effect but with multiple intersections.
This brings me to a possible alternative approach. At each intersection in
the crosstab style grid on the screen, you could have a combo box, perhaps
with multiple columns, which could show you every piece of data related to
that intersection. You could even have more than one such combo box at the
intersection if there are different sets of related data to be shown.
Once again, this type of form seems entirely obvious and intuitive to the
user, but represents a significant challenge to any but rather expert
programmers. To distill complexity down to simple, intuitive functionality
seems to me to be the most rewarding of programming tasks in database
front-end design.
Tom Ellison
Microsoft Access MVP
