Crosstab with subquery as source to another query

[HartJF]

I have created a crosstab that uses a subquery as its source and contains a
PIVOT... IN clause, which executes successfully. I have a query that selects
certain records from that crosstab and performs calculations on its fields,
but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM
verb. I tried simplifying the select query wrapper, as SELECT * FROM
([crosstab query]);, which fails with the same message. Could the subquery
be so complex that it frustrates the query optimizer? (The subquery JOINs
two tables and LEFT JOINs a third, and selects records based on hard-coded
dates. The production version will be a temporary, dynamically-created query
in VBA.) Must I convert the subquery to a make-(temporary)table query and
use that table as input to the crosstab? If I must make a temporary table,
I'd prefer to store the results of the crosstab, but if I can't SELECT *, I
don't expect to be able to SELECT INTO. I don't understand how the crosstab
can run by itself, but fails when embedded.

 

[Daryl S]

HartJF -

Crosstab queries are different from other queries as their column headings
can change between runs. This means using a crosstab query as a source for
other queries has some quirks. If your column headings of the crosstab query
will always be the same, then you can use the ColumnHeadings property to
explicitly define them (this also puts these column headings in the result
set even if this run doesn't contain all columns), and that can make it
easier for the query that uses the crosstab query as a source. You may also
need to explicity define the parameters to the crosstab query. Look up help
for ColumnHeadings and also define your parameters and see if that helps.
Some queries get too complex anyway, so you may need to go the maketable
route anyway...

 

[HartJF]

Thanks for your quick response. I included an IN list in the PIVOT clause,
which is tantamount to setting the ColumnHeadings property. Without that
list, I was receiving an error message requiring a fixed set of column
headings. For testing purposes, I hard-coded all parameters. The crosstab
executes successfully on its own, but when I use the crosstab as a source for
a subsequent select query, the query fails. The word TRANSFORM is
highlighted, suggesting the location of the error. I don't understand why
something that works in one context fails in another.

HartJF -

Crosstab queries are different from other queries as their column headings
can change between runs. This means using a crosstab query as a source for
other queries has some quirks. If your column headings of the crosstab query
will always be the same, then you can use the ColumnHeadings property to
explicitly define them (this also puts these column headings in the result
set even if this run doesn't contain all columns), and that can make it
easier for the query that uses the crosstab query as a source. You may also
need to explicity define the parameters to the crosstab query. Look up help
for ColumnHeadings and also define your parameters and see if that helps.
Some queries get too complex anyway, so you may need to go the maketable
route anyway...

--
Daryl S

I have created a crosstab that uses a subquery as its source and contains a
PIVOT... IN clause, which executes successfully. I have a query that selects
certain records from that crosstab and performs calculations on its fields,
but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM
verb. I tried simplifying the select query wrapper, as SELECT * FROM
([crosstab query]);, which fails with the same message. Could the subquery
be so complex that it frustrates the query optimizer? (The subquery JOINs
two tables and LEFT JOINs a third, and selects records based on hard-coded
dates. The production version will be a temporary, dynamically-created query
in VBA.) Must I convert the subquery to a make-(temporary)table query and
use that table as input to the crosstab? If I must make a temporary table,
I'd prefer to store the results of the crosstab, but if I can't SELECT *, I
don't expect to be able to SELECT INTO. I don't understand how the crosstab
can run by itself, but fails when embedded.

 

[Tokyo Alex]

Just tested this with very simple queries, and get the same error you do.

It looks like Access will not let you use a crosstab as a subquery.

Saving the crosstab and then using SELECT * FROM qdxCrossTab works (at least
with my very simple queries).

Testing done in Access 2007, which may make a difference.

Hope this helps,
Alex.

Thanks for your quick response. I included an IN list in the PIVOT clause,
which is tantamount to setting the ColumnHeadings property. Without that
list, I was receiving an error message requiring a fixed set of column
headings. For testing purposes, I hard-coded all parameters. The crosstab
executes successfully on its own, but when I use the crosstab as a source for
a subsequent select query, the query fails. The word TRANSFORM is
highlighted, suggesting the location of the error. I don't understand why
something that works in one context fails in another.

HartJF -

Crosstab queries are different from other queries as their column headings
can change between runs. This means using a crosstab query as a source for
other queries has some quirks. If your column headings of the crosstab query
will always be the same, then you can use the ColumnHeadings property to
explicitly define them (this also puts these column headings in the result
set even if this run doesn't contain all columns), and that can make it
easier for the query that uses the crosstab query as a source. You may also
need to explicity define the parameters to the crosstab query. Look up help
for ColumnHeadings and also define your parameters and see if that helps.
Some queries get too complex anyway, so you may need to go the maketable
route anyway...

--
Daryl S

I have created a crosstab that uses a subquery as its source and contains a
PIVOT... IN clause, which executes successfully. I have a query that selects
certain records from that crosstab and performs calculations on its fields,
but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM
verb. I tried simplifying the select query wrapper, as SELECT * FROM
([crosstab query]);, which fails with the same message. Could the subquery
be so complex that it frustrates the query optimizer? (The subquery JOINs
two tables and LEFT JOINs a third, and selects records based on hard-coded
dates. The production version will be a temporary, dynamically-created query
in VBA.) Must I convert the subquery to a make-(temporary)table query and
use that table as input to the crosstab? If I must make a temporary table,
I'd prefer to store the results of the crosstab, but if I can't SELECT *, I
don't expect to be able to SELECT INTO. I don't understand how the crosstab
can run by itself, but fails when embedded.

 

[Daryl S]

HartJF -

The only other suggestion is explicitly adding parameters (if you are
pulling data from a form or using run-time parameters in the crosstab query),
then make sure they are in the defined Parameters for that query. Otherwise
I can't think of anything else to try other than your temporary maketable
one...

--
Daryl S

Thanks for your quick response. I included an IN list in the PIVOT clause,
which is tantamount to setting the ColumnHeadings property. Without that
list, I was receiving an error message requiring a fixed set of column
headings. For testing purposes, I hard-coded all parameters. The crosstab
executes successfully on its own, but when I use the crosstab as a source for
a subsequent select query, the query fails. The word TRANSFORM is
highlighted, suggesting the location of the error. I don't understand why
something that works in one context fails in another.

HartJF -

Crosstab queries are different from other queries as their column headings
can change between runs. This means using a crosstab query as a source for
other queries has some quirks. If your column headings of the crosstab query
will always be the same, then you can use the ColumnHeadings property to
explicitly define them (this also puts these column headings in the result
set even if this run doesn't contain all columns), and that can make it
easier for the query that uses the crosstab query as a source. You may also
need to explicity define the parameters to the crosstab query. Look up help
for ColumnHeadings and also define your parameters and see if that helps.
Some queries get too complex anyway, so you may need to go the maketable
route anyway...

--
Daryl S

I have created a crosstab that uses a subquery as its source and contains a
PIVOT... IN clause, which executes successfully. I have a query that selects
certain records from that crosstab and performs calculations on its fields,
but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM
verb. I tried simplifying the select query wrapper, as SELECT * FROM
([crosstab query]);, which fails with the same message. Could the subquery
be so complex that it frustrates the query optimizer? (The subquery JOINs
two tables and LEFT JOINs a third, and selects records based on hard-coded
dates. The production version will be a temporary, dynamically-created query
in VBA.) Must I convert the subquery to a make-(temporary)table query and
use that table as input to the crosstab? If I must make a temporary table,
I'd prefer to store the results of the crosstab, but if I can't SELECT *, I
don't expect to be able to SELECT INTO. I don't understand how the crosstab
can run by itself, but fails when embedded.

 

[HartJF]

Gentlemen...

I reverted the subquery upon which the crosstab was based to a saved query,
and everything worked. That subquery must have been too complex for Jet.

Thanks for your good work!

HartJF -

The only other suggestion is explicitly adding parameters (if you are
pulling data from a form or using run-time parameters in the crosstab query),
then make sure they are in the defined Parameters for that query. Otherwise
I can't think of anything else to try other than your temporary maketable
one...

--
Daryl S

Thanks for your quick response. I included an IN list in the PIVOT clause,
which is tantamount to setting the ColumnHeadings property. Without that
list, I was receiving an error message requiring a fixed set of column
headings. For testing purposes, I hard-coded all parameters. The crosstab
executes successfully on its own, but when I use the crosstab as a source for
a subsequent select query, the query fails. The word TRANSFORM is
highlighted, suggesting the location of the error. I don't understand why
something that works in one context fails in another.

HartJF -

Crosstab queries are different from other queries as their column headings
can change between runs. This means using a crosstab query as a source for
other queries has some quirks. If your column headings of the crosstab query
will always be the same, then you can use the ColumnHeadings property to
explicitly define them (this also puts these column headings in the result
set even if this run doesn't contain all columns), and that can make it
easier for the query that uses the crosstab query as a source. You may also
need to explicity define the parameters to the crosstab query. Look up help
for ColumnHeadings and also define your parameters and see if that helps.
Some queries get too complex anyway, so you may need to go the maketable
route anyway...

--
Daryl S


:

I have created a crosstab that uses a subquery as its source and contains a
PIVOT... IN clause, which executes successfully. I have a query that selects
certain records from that crosstab and performs calculations on its fields,
but it fails with a "Syntax error in FROM clause," highlighting the TRANSFORM
verb. I tried simplifying the select query wrapper, as SELECT * FROM
([crosstab query]);, which fails with the same message. Could the subquery
be so complex that it frustrates the query optimizer? (The subquery JOINs
two tables and LEFT JOINs a third, and selects records based on hard-coded
dates. The production version will be a temporary, dynamically-created query
in VBA.) Must I convert the subquery to a make-(temporary)table query and
use that table as input to the crosstab? If I must make a temporary table,
I'd prefer to store the results of the crosstab, but if I can't SELECT *, I
don't expect to be able to SELECT INTO. I don't understand how the crosstab
can run by itself, but fails when embedded.