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 25 Sep 2009 14:46 Karl: The following is the result of the code you suggested. Division Program 0Shipped 1Shipped 56110KW SSEE 0 56110KW SSEE INC E 0 56120EH CCOP 0 711AW SSEE INC E 0 711KW SSEE INC E 1 712DB SSEE 0 712MD OBD 0 712WB SSEE 1 712WB SSEE INC E 1 716MD COBLU 1 It appears to me that if no items were shipped, a column will list all of those "zero" results, if one item was shipped, a column will list that, and I assume if 2 items were shipped, another column would list those. Is there a way to consolidate the results into one column? "KARL DEWEY" wrote: > 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.
From: KARL DEWEY on 25 Sep 2009 16:49 >>Is there a way to consolidate the results into one column? But it will not be a crosstab but a totals query. SELECT tbl_History.Division, tbl_History.Program, Sum(Nz([tbl_History].[SHPTrans], 0)) AS [Shipped] FROM tbl_History GROUP BY tbl_History.Division, tbl_History.Program; -- Build a little, test a little. "Deb" wrote: > Karl: > > The following is the result of the code you suggested. > > Division Program 0Shipped 1Shipped > 56110KW SSEE 0 > 56110KW SSEE INC E 0 > 56120EH CCOP 0 > 711AW SSEE INC E 0 > 711KW SSEE INC E 1 > 712DB SSEE 0 > 712MD OBD 0 > 712WB SSEE 1 > 712WB SSEE INC E 1 > 716MD COBLU 1 > > It appears to me that if no items were shipped, a column will list all of > those "zero" results, if one item was shipped, a column will list that, and I > assume if 2 items were shipped, another column would list those. Is there a > way to consolidate the results into one column? > > "KARL DEWEY" wrote: > > > 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.
From: Deb on 2 Oct 2009 14:28 Hi Karl: That helped with a couple of the queries, but I still need a Crosstab query to display the number of items shipped by shipping priority. Here is the code: TRANSFORM Count(tbl_History.SHPTrans) AS Shipped SELECT tbl_History.Division, tbl_History.Program FROM tbl_History GROUP BY tbl_History.Division, tbl_History.Program PIVOT tbl_History.ShippingPriority; Here is the information from the table that the query pulls the information from. It's kind of skewed because of the width of the table, but you get the drift. Total of 10 items shipped, priority options are 1, 2, 3, CASREP, DTO. SHP Program Division EMR Requisition Priority 1 SSEE 56120DB 56110-052l N65236-8310-r6780 1 0 SSEE 56110KW CASREP 0 0 0 0 SSEE 56110KW 2 CCOP 56120EH 3 CCOP 56120EH DTO CCOP 56120EH 2 1 OBD 56120MD 56160-042N N65236-8310-E811 1 1 CCOP 56120EH 2 1 SSEE 56110KW 1 SSEE INC E 56110KW 3 1 SSEE INC E 56110AW CASREP 1 SSEE INC E 56110AW DTO 1 SSEE INC E 56120WB 561120-652D N65236-8312-D912 CASREP 1 SSEE INC E 56110KW 561120-536t n65236-2148-d815 1 SSEE 56120WB 1 COBLU 56160MD The query returns the following: Division Program <> 1 2 3 CASREP DTO 5 56110AW SSEE INC E 1 1 56110KW SSEE 1 0 0 56110KW SSEE INC E 1 0 56120DB SSEE 1 56120EH CCOP 1 0 0 56120MD OBD 1 56120WB SSEE 1 56120WB SSEE INC E 1 56160MD COBLU 1 Why is there a line at the top of the list with nothing in Division or Program, but containing a "5" under "<>" on that line? There are also three 1's in that column. Why is the column header "<>"? Some of the information actually appears to be correct, but I don't know why some of the information is incorrect. Please help!! "KARL DEWEY" wrote: > >>Is there a way to consolidate the results into one column? > But it will not be a crosstab but a totals query. > > SELECT tbl_History.Division, tbl_History.Program, > Sum(Nz([tbl_History].[SHPTrans], 0)) AS [Shipped] > FROM tbl_History > GROUP BY tbl_History.Division, tbl_History.Program; > > > -- > Build a little, test a little. > > > "Deb" wrote: > > > Karl: > > > > The following is the result of the code you suggested. > > > > Division Program 0Shipped 1Shipped > > 56110KW SSEE 0 > > 56110KW SSEE INC E 0 > > 56120EH CCOP 0 > > 711AW SSEE INC E 0 > > 711KW SSEE INC E 1 > > 712DB SSEE 0 > > 712MD OBD 0 > > 712WB SSEE 1 > > 712WB SSEE INC E 1 > > 716MD COBLU 1 > > > > It appears to me that if no items were shipped, a column will list all of > > those "zero" results, if one item was shipped, a column will list that, and I > > assume if 2 items were shipped, another column would list those. Is there a > > way to consolidate the results into one column? > > > > "KARL DEWEY" wrote: > > > > > 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.
From: KARL DEWEY on 2 Oct 2009 16:13 Your dataset has 16 records. 10 of these show shipped with a 1 in the column. 5 have 0 in the shipped column. 4 have nothing in the Priority column. 1 has nothing in the shipped column. 1 of the shipped items (Requisition - n65236-2148-d815) does not show a priority. You need to make sure your data is perfect or put traps. TRANSFORM Count(tbl_History.SHPTrans) AS Shipped SELECT tbl_History.Division, tbl_History.Program FROM tbl_History GROUP BY tbl_History.Division, tbl_History.Program PIVOT IIF(tbl_History.ShippingPriority Not In("1", "2", "3", "DTO", "CASREP"), "ERROR", tbl_History.ShippingPriority) HAVING tbl_History.SHPTrans > 1; -- Build a little, test a little. "Deb" wrote: > Hi Karl: > > That helped with a couple of the queries, but I still need a Crosstab query > to display the number of items shipped by shipping priority. Here is the > code: > > TRANSFORM Count(tbl_History.SHPTrans) AS Shipped > SELECT tbl_History.Division, tbl_History.Program > FROM tbl_History > GROUP BY tbl_History.Division, tbl_History.Program > PIVOT tbl_History.ShippingPriority; > > Here is the information from the table that the query pulls the information > from. It's kind of skewed because of the width of the table, but you get the > drift. Total of 10 items shipped, priority options are 1, 2, 3, CASREP, DTO. > > SHP Program Division EMR Requisition Priority > 1 SSEE 56120DB 56110-052l N65236-8310-r6780 1 > 0 > SSEE 56110KW CASREP > 0 > 0 > 0 > 0 > SSEE 56110KW 2 > CCOP 56120EH 3 > CCOP 56120EH DTO > CCOP 56120EH 2 > 1 OBD 56120MD 56160-042N N65236-8310-E811 1 > 1 CCOP 56120EH 2 > 1 SSEE 56110KW 1 > SSEE INC E 56110KW 3 > 1 SSEE INC E 56110AW CASREP > 1 SSEE INC E 56110AW DTO > 1 SSEE INC E 56120WB 561120-652D N65236-8312-D912 CASREP > 1 SSEE INC E 56110KW 561120-536t n65236-2148-d815 > 1 SSEE 56120WB > 1 COBLU 56160MD > > The query returns the following: > > Division Program <> 1 2 3 CASREP DTO > 5 > 56110AW SSEE INC E 1 1 > 56110KW SSEE 1 0 0 > 56110KW SSEE INC E 1 0 > 56120DB SSEE 1 > 56120EH CCOP 1 0 0 > 56120MD OBD 1 > 56120WB SSEE 1 > 56120WB SSEE INC E 1 > 56160MD COBLU 1 > > Why is there a line at the top of the list with nothing in Division or > Program, but containing a "5" under "<>" on that line? There are also three > 1's in that column. Why is the column header "<>"? Some of the information > actually appears to be correct, but I don't know why some of the information > is incorrect. Please help!! > > "KARL DEWEY" wrote: > > > >>Is there a way to consolidate the results into one column? > > But it will not be a crosstab but a totals query. > > > > SELECT tbl_History.Division, tbl_History.Program, > > Sum(Nz([tbl_History].[SHPTrans], 0)) AS [Shipped] > > FROM tbl_History > > GROUP BY tbl_History.Division, tbl_History.Program; > > > > > > -- > > Build a little, test a little. > > > > > > "Deb" wrote: > > > > > Karl: > > > > > > The following is the result of the code you suggested. > > > > > > Division Program 0Shipped 1Shipped > > > 56110KW SSEE 0 > > > 56110KW SSEE INC E 0 > > > 56120EH CCOP 0 > > > 711AW SSEE INC E 0 > > > 711KW SSEE INC E 1 > > > 712DB SSEE 0 > > > 712MD OBD 0 > > > 712WB SSEE 1 > > > 712WB SSEE INC E 1 > > > 716MD COBLU 1 > > > > > > It appears to me that if no items were shipped, a column will list all of > > > those "zero" results, if one item was shipped, a column will list that, and I > > > assume if 2 items were shipped, another column would list those. Is there a > > > way to consolidate the results into one column? > > > > > > "KARL DEWEY" wrote: > > > > > > > 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.
From: Deb on 22 Oct 2009 12:41
Karl: Many thanks -- once the data was fixed, the query worked perfectly! Now I need to use that crosstab to build another query. I'm trying to pull unit cost by Division and Program, and add that column based on the crosstab query. It does pull everything, but it adds all of the unit costs for the Division/Program, not just for the records pulled in the crosstab. Current code: SELECT ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program, ctqry_MthDrmPriority.[1], ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3], ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO, Sum(sqry_MonthlyRpt.UnitCost) AS SumOfUnitCost FROM ctqry_MthDrmPriority INNER JOIN sqry_MonthlyRpt ON ctqry_MthDrmPriority.Division = sqry_MonthlyRpt.Division GROUP BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program, ctqry_MthDrmPriority.[1], ctqry_MthDrmPriority.[2], ctqry_MthDrmPriority.[3], ctqry_MthDrmPriority.CASREP, ctqry_MthDrmPriority.DTO HAVING (((ctqry_MthDrmPriority.[1]) Is Not Null)) OR (((ctqry_MthDrmPriority.[2]) Is Not Null)) OR (((ctqry_MthDrmPriority.[3]) Is Not Null)) OR (((ctqry_MthDrmPriority.CASREP) Is Not Null)) OR (((ctqry_MthDrmPriority.DTO) Is Not Null)) ORDER BY ctqry_MthDrmPriority.Division, ctqry_MthDrmPriority.Program; I feel like I'm getting close, but just can't figure out how to pull only the specific unit costs for items in the crosstab query only. "KARL DEWEY" wrote: > Your dataset has 16 records. > 10 of these show shipped with a 1 in the column. > 5 have 0 in the shipped column. > 4 have nothing in the Priority column. > 1 has nothing in the shipped column. > 1 of the shipped items (Requisition - n65236-2148-d815) does not show a > priority. > > You need to make sure your data is perfect or put traps. > > TRANSFORM Count(tbl_History.SHPTrans) AS Shipped > SELECT tbl_History.Division, tbl_History.Program > FROM tbl_History > GROUP BY tbl_History.Division, tbl_History.Program > PIVOT IIF(tbl_History.ShippingPriority Not In("1", "2", "3", "DTO", > "CASREP"), "ERROR", tbl_History.ShippingPriority) > HAVING tbl_History.SHPTrans > 1; > > -- > Build a little, test a little. > > > "Deb" wrote: > > > Hi Karl: > > > > That helped with a couple of the queries, but I still need a Crosstab query > > to display the number of items shipped by shipping priority. Here is the > > code: > > > > TRANSFORM Count(tbl_History.SHPTrans) AS Shipped > > SELECT tbl_History.Division, tbl_History.Program > > FROM tbl_History > > GROUP BY tbl_History.Division, tbl_History.Program > > PIVOT tbl_History.ShippingPriority; > > > > Here is the information from the table that the query pulls the information > > from. It's kind of skewed because of the width of the table, but you get the > > drift. Total of 10 items shipped, priority options are 1, 2, 3, CASREP, DTO. > > > > SHP Program Division EMR Requisition Priority > > 1 SSEE 56120DB 56110-052l N65236-8310-r6780 1 > > 0 > > SSEE 56110KW CASREP > > 0 > > 0 > > 0 > > 0 > > SSEE 56110KW 2 > > CCOP 56120EH 3 > > CCOP 56120EH DTO > > CCOP 56120EH 2 > > 1 OBD 56120MD 56160-042N N65236-8310-E811 1 > > 1 CCOP 56120EH 2 > > 1 SSEE 56110KW 1 > > SSEE INC E 56110KW 3 > > 1 SSEE INC E 56110AW CASREP > > 1 SSEE INC E 56110AW DTO > > 1 SSEE INC E 56120WB 561120-652D N65236-8312-D912 CASREP > > 1 SSEE INC E 56110KW 561120-536t n65236-2148-d815 > > 1 SSEE 56120WB > > 1 COBLU 56160MD > > > > The query returns the following: > > > > Division Program <> 1 2 3 CASREP DTO > > 5 > > 56110AW SSEE INC E 1 1 > > 56110KW SSEE 1 0 0 > > 56110KW SSEE INC E 1 0 > > 56120DB SSEE 1 > > 56120EH CCOP 1 0 0 > > 56120MD OBD 1 > > 56120WB SSEE 1 > > 56120WB SSEE INC E 1 > > 56160MD COBLU 1 > > > > Why is there a line at the top of the list with nothing in Division or > > Program, but containing a "5" under "<>" on that line? There are also three > > 1's in that column. Why is the column header "<>"? Some of the information > > actually appears to be correct, but I don't know why some of the information > > is incorrect. Please help!! > > > > "KARL DEWEY" wrote: > > > > > >>Is there a way to consolidate the results into one column? > > > But it will not be a crosstab but a totals query. > > > > > > SELECT tbl_History.Division, tbl_History.Program, > > > Sum(Nz([tbl_History].[SHPTrans], 0)) AS [Shipped] > > > FROM tbl_History > > > GROUP BY tbl_History.Division, tbl_History.Program; > > > > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "Deb" wrote: > > > > > > > Karl: > > > > > > > > The following is the result of the code you suggested. > > > > > > > > Division Program 0Shipped 1Shipped > > > > 56110KW SSEE 0 > > > > 56110KW SSEE INC E 0 > > > > 56120EH CCOP 0 > > > > 711AW SSEE INC E 0 > > > > 711KW SSEE INC E 1 > > > > 712DB SSEE 0 > > > > 712MD OBD 0 > > > > 712WB SSEE 1 > > > > 712WB SSEE INC E 1 > > > > 716MD COBLU 1 > > > > > > > > It appears to me that if no items were shipped, a column will list all of > > > > those "zero" results, if one item was shipped, a column will list that, and I > > > > assume if 2 items were shipped, another column would list those. Is there a > > > > way to consolidate the results into one column? > > > > > > > > "KARL DEWEY" wrote: > > > > > > > > > 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. |