Dear Alec:
You must plan for the worst case. Your crosstab will have one or more
columns that filter the row, and one column that creates the column
headings. At the "intersection" of any row and column, you can place
one value. I suggest you place only the COUNT() of how many values
there are at that intersection.
How many values could there be? What if there were 10 values, 100
values, or 1000 values? You could not expect to display them all
inside a little box.
You could allow the crosstab to show only the number of values at each
intersection. Place this on a form. When the user clicks on any
intersection, you could use the values for row and column to filter a
subform and show all the rows that meet those criteria.
The alternative is to append together all the values in a single
column in your "pre query" so there is only one value to display. If
you enlarge the display at each intersection so it can show some
arbitrary maximum number of appended qty/job numbers, you face two
opposing dilemas. When you make the display large to hold a large
number of them, your overall display becomes huge with mostly empty
space. When you make it of a much smaller size, you cannot display it
when there are a large number of qty/job numbers to be shown.
In an interactive form you can overcome this difficulty by listing the
qty/job numbers in a separate subform, selectable by clicking on the
desired intersection. You can then scroll down though any length of
qty/job number selections.
In a report, don't use a crosstab. Use the date as the last group and
sort column. List all the qty/job numbers to the right of the columns
showing the grouped and sorted data. First of all, you may have a
large number of crosstab date columns, and this is difficult to
impossible to arrange in a report. On a form you could accomodate any
number of date columns with a horizontal scroll bar, dynamically
requerying the data if a user scrolls. On a piece of paper, nothing
like this is possible. Next, you cannot list all the qty/job number
values across. Reports have to go vertically. After all, that's the
way things are represented on paper. You have unlimited vertical
space, and very limited horizontal space.
Those are my observations from experience.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
