Crosstab queries 1

[ncopeland]

Hi

I have a query that currently runs that produces a list of products down the left side with store names printed across the top. Each store gets a certain number of each product for sale. I want to total the number of items that go to each store for sale. The Crosstab query does not allow me create a total line. I have been able to create a second crosstab query that just produces the total row. Is it possible to insert the first query's results to another query and then add the total line to the end of that query.

Any other ideas on how to do this would be very very useful.

Kind Regards

Neil Copeland.

 

[AlexCuse]

Perhaps something like this:

select * from crosstab1
union select * from crosstab2

I'm not sure if UNION works with crosstab queries, hence the need to treat the two queries like tables.

Hope this will help,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[ncopeland]

One further question to this solution. Is it possible to get the second query to be inserted at the end. At the minute the total row is being inserted in the middle of the first queries records. As this is a total line it needs to be displayed at the bottom.

Any ideas greatly received.

Kind Regards

NC.

 

[AlexCuse]

What I always do for something like this is in the first or last column, select something like 'a' in the first query, then 'z' or 'TOTAL' in the same position for the totals query. Then I use this field to order by, and of course exclude it from any reporting it is being used for.

Hope it helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[ncopeland]

Thanks Alex for the help. Within that field I would like to display ZTotal or something like that. Unfortunately it is pulling out the customers name. Is there anyway within the query to change it from the customer name to ZTotal.

Kind Regards


Neil Copeland.

 

[AlexCuse]

In your additional column, you want to be selecting simply a word in quotes. In the context of my original example, it would be something like this:

select crosstab1.*, "NOTTOTAL" as SortField from crosstab1
union select crosstab2.*, "ZTOTAL" as SortField from crosstab2
order by SortField

Let me know how this works out for you.

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.