CrossTab Queries: Multiple Fields?

[(PeteCresswell)]

I've done several reports and spreadsheet-creators driven by CrossTab queries.

e.g. A report shows totals for "funds" and we never know what or how many funds
will be present. A CrossTab query sums up the XYZ field for every fund and
flips the totals on their side so there's a column for each fund.

Works well and is pretty quick.

But now I've got a situation where we need six or seven totals for each fund and
my experience so far with CrossTab queries is that such a query will only
support one total.

My fallback has been to write VBA routines to retrieve whatever totals I need
on-the-fly and then plug them into the report (in this case a spreadsheet) as I
compute them. As I iterate through the funds, each time I hit a new fund I
feed the FundID and a couple of dates to one or more functions, and the
functions return the totals I need..... further complicated by each row of the
sheet representing a different entity which requires totals for different dates.

That works... but it's slow as death. OK for 30-40 entities, but when it gets
much over a hundred the user's going to have to take a coffee break or
something.

I should probably look to optimizing those functions... but I've also got to
wonder if there are CrossTab alternatives out there - something where I could
get the speed of a CrossTab query, but support many totals.

First thing that occurred to me was cascading a half-dozen CrossTab queries...
but at the time that was uncharted waters and I needed to get on with the
project.

Now I've got some deadline slack and I'm wondering if I should retrofit a better
solution.

Suggestions?

 

[Allen Browne]

A messy solution is to concatenate various values together as a string to
use as the Value in the crosstab. (This expression might be quite a long
one!)

It might be worth creating a temp table with all the columns you need (e.g.
7 totals x 8 funds would be 56 columns, plus your key column(s)), populate
the key column with an Append query, and then populate the other columns
with a series of crosstabs turned into Update queries.

 

[Ken Sheridan]

Pete:

You might be able to use separate cross tab queries for each type of total
and join them on the 'row heading' column from each. The following is an
example which joins two crosstab queries, one of which returns the number of
feet drilled per city over a three monthly period, the other the number of
hours drilled. The two are joined on City in a query which returns both
values along with the feet/hours ratios:

SELECT
qryDrillLog_Feet.City,
qryDrillLog_Feet.[Jan 2006] AS [Jan 2006_Feet],
qryDrillLog_Hours.[Jan 2006] AS [Jan 2006_Hours],
Format(NZ(qryDrillLog_Feet.[Jan 2006]/
qryDrillLog_Hours.[Jan 2006],0),"Fixed")
AS [FeetPerHour_Jan 2006],
qryDrillLog_Feet.[Feb 2006] AS [Feb 2006_Feet],
qryDrillLog_Hours.[Feb 2006] AS [Feb 2006_Hours],
Format(NZ(qryDrillLog_Feet.[Feb 2006]/
qryDrillLog_Hours.[Feb 2006],0),"Fixed")
AS [FeetPerHour_Feb 2006],
qryDrillLog_Feet.[Mar 2006] AS [Mar 2006_Feet],
qryDrillLog_Hours.[Mar 2006] AS [Mar 2006_Hours],
Format(NZ(qryDrillLog_Feet.[Mar 2006]/
qryDrillLog_Hours.[Mar 2006],0),"Fixed")
AS [FeetPerHour_Mar 2006]
FROM qryDrillLog_Feet, qryDrillLog_Hours
WHERE qryDrillLog_Feet.City = qryDrillLog_Hours.City;

You don't say whether or not you are using an IN clause in your crosstab
query to return a column for every fund whether or not they have any data for
the any of the row heading values. A generic query joining the crosstabs
would really require that they do include an IN clause as the column headings
are then known, though if not it should be possible to generate the SQL for
the final query with some code, determining what columns are returned by the
crosstabs by iterating through the Fields collection of the QueryDef object,
and generating a set of columns in the final query for each.

Ken Sheridan
Stafford, England

 

[AP]

I've done several reports and spreadsheet-creators driven byCrossTabqueries.

e.g. A report shows totals for "funds" and we never know what or how many funds
will be present. ACrossTabquery sums up the XYZ field for every fund and
flips the totals on their side so there's a column for each fund.

Works well and is pretty quick.

But now I've got a situation where we need six or seven totals for each fund and
my experience so far withCrossTabqueries is that such a query will only
support one total.

My fallback has been to write VBA routines to retrieve whatever totals I need
on-the-fly and then plug them into the report (in this case a spreadsheet) as I
compute them. As I iterate through the funds, each time I hit a new fund I
feed the FundID and a couple of dates to one or more functions, and the
functions return the totals I need..... further complicated by each row of the
sheet representing a different entity which requires totals for different dates.

That works... but it's slow as death. OK for 30-40 entities, but when it gets
much over a hundred the user's going to have to take a coffee break or
something.

I should probably look to optimizing those functions... but I've also got to
wonder if there areCrossTabalternatives out there - something where I could
get the speed of aCrossTabquery, but support many totals.

First thing that occurred to me was cascading a half-dozenCrossTabqueries...
but at the time that was uncharted waters and I needed to get on with the
project.

Now I've got some deadline slack and I'm wondering if I should retrofit a better
solution.

Suggestions?

Hi Pete:
This is an add-in they may be able to do what you are looking for. It
is something that was developed to essentially pivot data within a
query, allowing a crosstab on an unlimited number of data columns
(provided you stay within the 255 Access max.)
see
www.megacrosstab.com fpr more information.

 

[Joan Wild]

Perhaps this will help
http://www.access.hookom.net/ArtMultiValueXtab.htm