Crosstab - What am I doing wrong?
in [Queries]
|
Prev: Do I need to convert a hyperlink field to text to change it?
Next: Join a table, queryfails with "Query input must contain at least one table or query
From: Deb on 24 Sep 2009 12:36 I believe I'm going to have to create several crosstab queries in order to develop the monthly report required by the client. Here is SQL statement for one of the crosstabs: TRANSFORM Sum(tbl_History.SHPTrans) AS Expr2 SELECT tbl_History.Division, tbl_History.Program FROM tbl_History GROUP BY tbl_History.Division, tbl_History.Program PIVOT ([SHPTrans]) & "Shipped"; The cross tab appears to be working, (I need Division & Program rows - and that part appears to be working), but when I run the query, I get the following. It's like the "value" column displays with no information in it, the values are in the "1Shipped" column (as they should be). Division Program 1Shipped Shipped 56110KW SSEE 56110KW SSEE INC E 56120EH CCOP 711AW SSEE INC E 711KW SSEE INC E 1 712DB SSEE 712MD OBD 712WB SSEE 1 712WB SSEE INC E 1 I think I'm getting close to getting what I need, I'm just missing a little something. Please advise.
From: KARL DEWEY on 24 Sep 2009 14:27 What is the datatype of [SHPTrans] field? Post sample data. -- Build a little, test a little. "Deb" wrote: > I believe I'm going to have to create several crosstab queries in order to > develop the monthly report required by the client. > > Here is SQL statement for one of the crosstabs: > > TRANSFORM Sum(tbl_History.SHPTrans) AS Expr2 > SELECT tbl_History.Division, tbl_History.Program > FROM tbl_History > GROUP BY tbl_History.Division, tbl_History.Program > PIVOT ([SHPTrans]) & "Shipped"; > > The cross tab appears to be working, (I need Division & Program rows - and > that part appears to be working), but when I run the query, I get the > following. It's like the "value" column displays with no information in it, > the values are in the "1Shipped" column (as they should be). > > Division Program 1Shipped Shipped > 56110KW SSEE > 56110KW SSEE INC E > 56120EH CCOP > 711AW SSEE INC E > 711KW SSEE INC E 1 > 712DB SSEE > 712MD OBD > 712WB SSEE 1 > 712WB SSEE INC E 1 > > I think I'm getting close to getting what I need, I'm just missing a little > something. > > Please advise.
From: KARL DEWEY on 24 Sep 2009 15:31 I just noticed you have [SHPTrans] as pivot and value. You probably have some nulls. Anyway your data would look like this -- Division Program 1Shipped 2Shipped 3Shipped 4Shipped 56110KW SSEE 2 56110KW SSEE INC E 1 56120EH CCOP 3 711AW SSEE INC E 4 -- Build a little, test a little. "KARL DEWEY" wrote: > What is the datatype of [SHPTrans] field? Post sample data. > -- > Build a little, test a little. > > > "Deb" wrote: > > > I believe I'm going to have to create several crosstab queries in order to > > develop the monthly report required by the client. > > > > Here is SQL statement for one of the crosstabs: > > > > TRANSFORM Sum(tbl_History.SHPTrans) AS Expr2 > > SELECT tbl_History.Division, tbl_History.Program > > FROM tbl_History > > GROUP BY tbl_History.Division, tbl_History.Program > > PIVOT ([SHPTrans]) & "Shipped"; > > > > The cross tab appears to be working, (I need Division & Program rows - and > > that part appears to be working), but when I run the query, I get the > > following. It's like the "value" column displays with no information in it, > > the values are in the "1Shipped" column (as they should be). > > > > Division Program 1Shipped Shipped > > 56110KW SSEE > > 56110KW SSEE INC E > > 56120EH CCOP > > 711AW SSEE INC E > > 711KW SSEE INC E 1 > > 712DB SSEE > > 712MD OBD > > 712WB SSEE 1 > > 712WB SSEE INC E 1 > > > > I think I'm getting close to getting what I need, I'm just missing a little > > something. > > > > Please advise.
From: Deb on 24 Sep 2009 16:57 Hi Karl! Thanks for responding. The data type is number, long integer. It records the number of items shipped. There are some nulls in that field. Would it make any difference to set the default as "0"? I'm going to have to do several crosstab queries and combine those for the report. I've built a second crosstab and it pretty much does the same thing. They are pulling in the correct number of items, I just don't know why it seems to be adding the blank column? "KARL DEWEY" wrote: > I just noticed you have [SHPTrans] as pivot and value. You probably have > some nulls. > Anyway your data would look like this -- > Division Program 1Shipped 2Shipped 3Shipped 4Shipped > 56110KW SSEE 2 > 56110KW SSEE INC E 1 > 56120EH CCOP 3 > 711AW SSEE INC E 4 > > > -- > Build a little, test a little. > > > "KARL DEWEY" wrote: > > > What is the datatype of [SHPTrans] field? Post sample data. > > -- > > Build a little, test a little. > > > > > > "Deb" wrote: > > > > > I believe I'm going to have to create several crosstab queries in order to > > > develop the monthly report required by the client. > > > > > > Here is SQL statement for one of the crosstabs: > > > > > > TRANSFORM Sum(tbl_History.SHPTrans) AS Expr2 > > > SELECT tbl_History.Division, tbl_History.Program > > > FROM tbl_History > > > GROUP BY tbl_History.Division, tbl_History.Program > > > PIVOT ([SHPTrans]) & "Shipped"; > > > > > > The cross tab appears to be working, (I need Division & Program rows - and > > > that part appears to be working), but when I run the query, I get the > > > following. It's like the "value" column displays with no information in it, > > > the values are in the "1Shipped" column (as they should be). > > > > > > Division Program 1Shipped Shipped > > > 56110KW SSEE > > > 56110KW SSEE INC E > > > 56120EH CCOP > > > 711AW SSEE INC E > > > 711KW SSEE INC E 1 > > > 712DB SSEE > > > 712MD OBD > > > 712WB SSEE 1 > > > 712WB SSEE INC E 1 > > > > > > I think I'm getting close to getting what I need, I'm just missing a little > > > something. > > > > > > Please advise.
From: KARL DEWEY on 24 Sep 2009 18:41
Try this -- TRANSFORM Sum(Nz([tbl_History].[SHPTrans], 0)) AS Expr2 SELECT tbl_History.Division, tbl_History.Program FROM tbl_History GROUP BY tbl_History.Division, tbl_History.Program PIVOT (Nz([SHPTrans], 0)) & " Shipped"; -- Build a little, test a little. "Deb" wrote: > Hi Karl! Thanks for responding. > > The data type is number, long integer. It records the number of items > shipped. There are some nulls in that field. Would it make any difference > to set the default as "0"? I'm going to have to do several crosstab queries > and combine those for the report. I've built a second crosstab and it pretty > much does the same thing. They are pulling in the correct number of items, I > just don't know why it seems to be adding the blank column? > > "KARL DEWEY" wrote: > > > I just noticed you have [SHPTrans] as pivot and value. You probably have > > some nulls. > > Anyway your data would look like this -- > > Division Program 1Shipped 2Shipped 3Shipped 4Shipped > > 56110KW SSEE 2 > > 56110KW SSEE INC E 1 > > 56120EH CCOP 3 > > 711AW SSEE INC E 4 > > > > > > -- > > Build a little, test a little. > > > > > > "KARL DEWEY" wrote: > > > > > What is the datatype of [SHPTrans] field? Post sample data. > > > -- > > > Build a little, test a little. > > > > > > > > > "Deb" wrote: > > > > > > > I believe I'm going to have to create several crosstab queries in order to > > > > develop the monthly report required by the client. > > > > > > > > Here is SQL statement for one of the crosstabs: > > > > > > > > TRANSFORM Sum(tbl_History.SHPTrans) AS Expr2 > > > > SELECT tbl_History.Division, tbl_History.Program > > > > FROM tbl_History > > > > GROUP BY tbl_History.Division, tbl_History.Program > > > > PIVOT ([SHPTrans]) & "Shipped"; > > > > > > > > The cross tab appears to be working, (I need Division & Program rows - and > > > > that part appears to be working), but when I run the query, I get the > > > > following. It's like the "value" column displays with no information in it, > > > > the values are in the "1Shipped" column (as they should be). > > > > > > > > Division Program 1Shipped Shipped > > > > 56110KW SSEE > > > > 56110KW SSEE INC E > > > > 56120EH CCOP > > > > 711AW SSEE INC E > > > > 711KW SSEE INC E 1 > > > > 712DB SSEE > > > > 712MD OBD > > > > 712WB SSEE 1 > > > > 712WB SSEE INC E 1 > > > > > > > > I think I'm getting close to getting what I need, I'm just missing a little > > > > something. > > > > > > > > Please advise. |