Problem using a crosstab query as a sub query

[Robin9876]

In a crosstab query that has the column heading set to customer name
with a where clause a parametrised date range and sums the total
orders. This is then used as a sub query in another standard query and
there is only a few customers.

When a date range is used that a particular customer has not placed
any orders the customer name does not appear as a field in the
crosstab query and therefore can not be used in the other query.


Is it possible to code either of the queries so that if any of the
known field names does not exist it returns a 0 value for the sum.

 

[Wolfgang Kais]

Hello "Robin9876".

In a crosstab query that has the column heading set to customer
name with a where clause a parametrised date range and sums the
total orders. This is then used as a sub query in another standard
query and there is only a few customers.

When a date range is used that a particular customer has not placed
any orders the customer name does not appear as a field in the
crosstab query and therefore can not be used in the other query.

Is it possible to code either of the queries so that if any of the
known field names does not exist it returns a 0 value for the sum.

You can define what column headings a crosstab query returns by
using an IN list in the PIVOT clause.
This should be done only if the subquery returns only data specific
to that single customer (or a fixed set of customers), because this
is not flexible at all (it won't return new costomers), and it
returns NULL if no records for that customer exist.
Why should a query work this way? Do you have to create an new such
query for every new customer? Then either the query can be designed
another way or you should create a new entire database for every new
customer.

 

[Robin9876]

The pivot line is currently the following, how would the names be
added to this line?

PIVOT myTable.Customer;

It is required for various reporting options. I have changed the
description from the actual system to a sales order type system which
people would easily understand.

 

[Robin9876]

Typical as soon as I hit the submit before I found how to do it.

However now I can see the query in SQL view but when trying to switch
to design view the following error message is displayed.

The expression you entered exceeds the 1,024-character limit for the
query design grid.

The pivot line is currently the following, how would the names be
added to this line?

PIVOT myTable.Customer;

It is required for various reporting options. I have changed the
description from the actual system to a sales order type system which
people would easily understand.

Hello "Robin9876".

You can define what column headings a crosstab query returns by
using an IN list in the PIVOT clause.
This should be done only if the subquery returns only data specific
to that single customer (or a fixed set of customers), because this
is not flexible at all (it won't return new costomers), and it
returns NULL if no records for that customer exist.
Why should a query work this way? Do you have to create an new such
query for every new customer? Then either the query can be designed
another way or you should create a new entire database for every new
customer.

 

[Wolfgang Kais]

Hello "Robin9876".

Typical as soon as I hit the submit before I found how to do it.

However now I can see the query in SQL view but when trying to
switch to design view the following error message is displayed.

The expression you entered exceeds the 1,024-character limit for
the query design grid.

Store the crosstab query as a standalone query in the database and
use something like (Select * From TheCrosstabQuery) as the subquery.
I guess this helps for the design view thing. But I still do believe
that the whole query can be written without a crosstab query as a
subquery. How is the crosstab query used in the main query?

 

[Robin9876]

It is part of some data processing, the next step is to store the
results in a table. However as it is a 3rd party database I don't
want to change it any further.