Crosstab Query 1

[ncopeland]

Hi

I have a crosstab query that shows product down the left side and store names across the top. I want to total by product down the right hand side of the query.

Is this possible.

Kind Regards


NC.

 

[AlexCuse]

I believe this is possible. Can you post your SQL?

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[ncopeland]

Hi Alex

TRANSFORM Sum(qry_MSDelNote_1.[Order Qty]) AS [Sum Of Order Qty]
SELECT qry_MSDelNote_1.Description, qry_MSDelNote_1.[Product Code]
FROM qry_MSDelNote_1
GROUP BY qry_MSDelNote_1.Description, qry_MSDelNote_1.[Product Code]
PIVOT qry_MSDelNote_1.Store;


Plan started off with a total line at bottom. Now total line required at bottom and a total column down the left side.

Thanks

NC.

 

[AlexCuse]

And what does your data look like? Just give an example of one row from the query you're selecting from, and your desired output.

I have only ever done this with counts, but I think it must be possible.

A wise man once said
"The only thing normal about database guys is their tables".

 

[ncopeland]

Hi Alex

Input Data

First Record

Description Test-1
Product Test-2
Store Location-1
Order Qty 10

Second Record

Description Test-1
Product Test-2
Store Location-3
Order Qty 5

Above gives example of 2 records. You may have the same product going to 13 stores.

So Desired output

Description Product Location-1 Location-3 Total
Test-1 Test-2 10 5 15

The total column is the one I am looking for. Hope this makes sense.

Kind Regards


NC.

 

[AlexCuse]

Can you try running this and see what you get?

TRANSFORM Sum(qry_MSDelNote_1.[Order Qty]) AS [Sum Of Order Qty]
SELECT qry_MSDelNote_1.Description, qry_MSDelNote_1.[Product Code][COLOR=red],Sum(qry_MSDelNote_1.[Order Qty]) AS TOTAL[/color]
FROM qry_MSDelNote_1
GROUP BY qry_MSDelNote_1.Description, qry_MSDelNote_1.[Product Code]
PIVOT qry_MSDelNote_1.Store;

This is the way I include a total when I am transforming on counts. It may work or it may not. (Hopefully yes )

A wise man once said
"The only thing normal about database guys is their tables".

 

[ncopeland]

Hi Alex

Superb thanks for all the help. Have now got it working the way I want.

Kind Regards

NC.