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 said:
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 said:
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!!
:
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.
:
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?
:
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.
:
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?
:
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.
:
What is the datatype of [SHPTrans] field? Post sample data.
--
Build a little, test a little.
:
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.
