REPOST: Error 2329 (To create a crosstab query ...)

[Guest]

I've got a couple of crosstab querys that run great from the query design
mode. However, when I try to use the OutputTo method, I get the following
error message:

To create a crosstab query, you must specify one of more Row heading(s)
options, one column heading option, and one Value Option.

I've got all of these in the query, there are no parameters to set. Anyone
know why the OutputTo method is not recognizing these queries properly?

All three of these queries are based on another (Union) query, but they all
run properly from the query view. I even went so far as to copy the SQL,
paste it into a new query, and rename it. But that didn't work either.

Any help would be greatly appreciated.

Dale

 

[Gary Walter]

I've got a couple of crosstab querys that run great from the query design
mode. However, when I try to use the OutputTo method, I get the following
error message:

To create a crosstab query, you must specify one of more Row heading(s)
options, one column heading option, and one Value Option.

I've got all of these in the query, there are no parameters to set.
Anyone
know why the OutputTo method is not recognizing these queries properly?

All three of these queries are based on another (Union) query, but they
all
run properly from the query view. I even went so far as to copy the SQL,
paste it into a new query, and rename it. But that didn't work either.

Hi Dale,

Probably not what you were hoping for, but...

I might try appending results of Union query
to a temp table, then try outputting xtab(s) based on
temp table (if that works)...

I'm sorry I can not give definitive reason...
it just always seemed like xtabs have to jump
through a lot of hidden hoops that get cured
by an occasional temp table...

I don't have much experience with OutputTo method... is there
another possible way to accomplish the same thing?

good luck,

gary

 

[John Spencer]

My GUESS would be that the outputTo function does not know what the fields
are since you are using a crosstab query and the fields can vary depending
on the data.

One thing I might try would be to specify the "names" of the columns
generated by the column heading option. Otherwise, I think you will have to
generate a table with the data and then export that. Of course, specifying
the column names (see Pivot line below) means that you will always have to
have the same columns.

TRANSFORM ...
SELECT ...
WHERE ...
GROUP ...
PIVOT Table.Words ("A","B","Charlie","appleSauce")



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Gary,

Looks like that is what I am going to have to do, although I really hate
having to create temporary tables in Access. I had already tried the Pivot
IN ( ) clause in the queries, and that was not solving the problem.

Now I'm creating a temp table on the fly, running the OutputTo method, and
then deleting the temp table. Everything seems to be working fine. Will
have to consider writing this temp table to another database, when I get a
chance.

Thanks for the idea.

Dale