Crosstab Query - Group Headings

[Craig Hornish]

I have a few reports that use a crosstab query that can change the column
headings depending on different criteria.

The standard way that this is done is that the recordsource that is in the
properties window is also as a recordset when the form is opened, and that
"recordset" is used to populate columns, and the duplication of the
recordsourse is used so that the detail lines, and groupings will go through
each line.

Two related problems are that recordsource gets evaluated each time when
you:
switch to design view and run the report- Did I mention I have a
complicated Crosstab - 20 seconds to more than a minute on various reports
plus the time for the recordset to evaluate.

In a simpler report I have solved the recordsource problems by having NO
recordsource and using the detail Print section setting the MoveNext,
PrintSection, and MoveLayout and setting them appropriately to print out the
everything in the recordset. But in these I need to print a group
eading. -

Question 1 - In this situation how can I force the Group header to
print?

That is the most desirable solution, but a couple alternate solutions are ;

Question 2 - Can I assign a Crosstab Query in SQL as a recordsource? and
How?
I have tried taking the SQL text from the crosstab and get an
error "Cannot use the crosstab of a non-fixed column as a subquery"
Or
Question 3 - Can I stop Access from evaluating the recordsource in
design view.

Thanks

Craig Hornish

 

[Duane Hookom]

Consider checking out the Crosstab report solutions found at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4. The Crosstab.mdb is
much more flexible and efficient than your current method and requires less
code.

 

[Craig Hornish]

Duane
Well I'll see if I can adapt the report to what I do. But on the face
of things this one does not meet my needs.

Applying my case to this one -- each Sales person can have the same
customer or not - but I want to show all who have activity. And then the
big one - - I need the total for all sales persons for a particular
Customer.

Sales Report by Empoloyee and Customer
Carson Custommer 16 Customer 21 Customer 43
1/26/2000 94 107

total 94 107 0
Cash
1/23/2000 45* 38
total 45 38
Grand total 94 152 38

It would be better if the customer -was not on a second level below but on
another page - but this can be done by limiting the range of customers
returned.

I only reviewed this quickly so Question 1 can still be answered by anyone.

The "alias" method looks interesting -and I will test it out - but I will
have to see how it works against what I have - 23,000 and 100,000 + records
in two tables and 4 criteria in a crosstab - but first I need to make sure I
can do the Grand totals, and correct layout.

Craig Hornish

 

[Duane Hookom]

If you need customer totals at the end then you should be able to create a
subreport of just these totals and place it in the footer of your main
report.

 

[Craig Hornish]

Remember I am trying to reduce the number of times the crosstab has to run.
With my 100,000 records it takes 20 seconds to run just the inital query
part now I am adding another query for the footer - and now I am back to the
begining.
Speed is the key right now - still haven't adapted the alias code yet so
I'll see what I can do after that adjustment.

Craig Hornish