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 27 Oct 2009 12:51 Would a subquery be in order to pull only the unit costs from the MonthlyRpt query? "Deb" wrote: > 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. |