Crosstab query UNION

[jim]

I have created a crosstab query using a table with the
following elements:

charge incurred date
charge paid date
charge amount

with a paid month for each row pivotting on incurred
month. The result looks like:

mar-02 apr-02 may-02(incurred)

may-03 100.00 250.00 200.00
jun-03 75.00 125.00 100.00
(paid)

The query prompts the user for a reporting date through a
parameter and then returns rows for all paid months for
the previous 3 years using the input date. The same 3
year period is used to determine which and how many
columns (incurred date) will be displayed.

This query works fine. I've now been asked to add a
column which will contain a total of all incurred charges
outside the 3 year window for each paid month.

I was unable to figure a way to incorporate this column
into the existing crosstab query so I created a new query
that just returns this one column of all incurred dates
outside my 3 year range.

When I try to do a union between the old crosstab query
and the new query, I get a TRANSFORM syntax error. I
tried creating the new query as both a simple query and a
crosstab and tried to UNION each. My guess is that a
UNION cannot be done with a crosstab query.

Any suggestions as to how to get this new column into my
query. The client does not want a report, they want a
query which will be exported into excel.

Thanks,
Jim

 

[Duane Hookom]

I expect it might work if you entered all of your column headings into the
crosstab. You may need to create a temporary table from the crosstab and use
it in the union query. You could also use automation to push the values out
to Excel.