Showing all Rows & Columns in a crosstab query.

[coach.32]

I have the following code for one of my Crosstab queries. My question relates to the 'PIVOT' part of the code. Am I able to include another PIVOT argument that will show all the rows? Do I add 'AND to the argument? I need to show all rows and columns. Thank you for any assistance you can offer.

TRANSFORM CLng(Nz(Count([01 REG COMP - 32477].Acty_Cntct_Typ_Nm),0)) AS CountOfActy_Cntct_Typ_Nm
SELECT [01 REG COMP - 32477].Intractn_Sts, Count([01 REG COMP - 32477].Acty_Cntct_Typ_Nm) AS [Total Of Acty_Cntct_Typ_Nm]
FROM [01 REG COMP - 32477]
GROUP BY [01 REG COMP - 32477].Intractn_Sts
PIVOT [01 REG COMP - 32477].Rating In ("P1","P2","P3","P4","P5","P6","P7","P8","P9","PO ASAP");

 

[stopher]

You mean you want rows to appear for what you haven't got records for?

e.g. Here, there are no records in the source data for London:

                  Sales
Bristol            34
London 
Manchester         46
York               80

You can do this by left joining a list of towns with your transform query. So you just need to make a list of towns (or whatever rows you want to appear).

hth
Chris

 

[coach.32]

Thank-you for your advice. I have tried the JOIN command but I am not having much luck. Using my SQL could you please give me an example of how to word this? Thank-you for your patience.

 

[stopher]

Suppose you have a table called tblIntractn_Sts with a column called Intractn_Sts. And in this column we have listed all the Intractn_Sts that we want to appear in the pivot.

Then the pivot will look like this:

TRANSFORM CLng(Nz(Count([01 REG COMP - 32477].Acty_Cntct_Typ_Nm),0)) AS CountOfActy_Cntct_Typ_Nm
SELECT tblIntractn_Sts.Intractn_Sts, Count([01 REG COMP - 32477].Acty_Cntct_Typ_Nm) AS [Total Of Acty_Cntct_Typ_Nm]
FROM [01 REG COMP - 32477] RIGHT JOIN tblIntractn_Sts ON [01 REG COMP - 32477].Intractn_Sts = tblIntractn_Sts.Intractn_Sts
GROUP BY tblIntractn_Sts.Intractn_Sts
PIVOT [01 REG COMP - 32477].Rating In ("P1","P2","P3","P4","P5","P6","P7","P8","P9","PO ASAP");

Copy and paste the above into a new query and run it to see if it works. Take a look in design view and you will see how the new table is joined with an arrow (Right join). You can double click on the arrow to see the description of the relationship.

You might already have a table that lists the Intractn_Sts that you want. In which case you can use that instead of creating a new table.

hth
Chris