David McAfee
davidmcafee at gmail.com
Thu Sep 30 19:31:17 CDT 2010
I'm working in an ADP.
I have the pseudo pivot table since I am working in SQL2000 (PIVOT
wasn't available until SQL2005)
SELECT
SplitID, IncDetID, SplitTypeID, SplitAmt
FROM tblSplit
WHERE IncDetID = 5199
returns
SplitID IncDetID SplitTypeID SplitAmt
---------- ------------- ---------------- -------------
36 5199 1 15.00
37 5199 7 5.00
My pseudo pivot table:
SELECT IncDetID,
SUM(CASE SplitTypeID WHEN 1 THEN SplitAmt ELSE 0 END) AS DlrPay,
SUM(CASE SplitTypeID WHEN 2 THEN SplitAmt ELSE 0 END) AS SvcMgr,
SUM(CASE SplitTypeID WHEN 3 THEN SplitAmt ELSE 0 END) AS PartsDept,
SUM(CASE SplitTypeID WHEN 4 THEN SplitAmt ELSE 0 END) AS SvcAdv,
SUM(CASE SplitTypeID WHEN 5 THEN SplitAmt ELSE 0 END) AS SvcDept,
SUM(CASE SplitTypeID WHEN 6 THEN SplitAmt ELSE 0 END) AS SvcTech,
SUM(CASE SplitTypeID WHEN 7 THEN SplitAmt ELSE 0 END) AS Contest
FROM tblSPlit
GROUP BY IncDetID
returns:
IncDetID DlrPay SvcMgr PartsDept SvcAdv SvcDept SvcTech Contest
------------ ----------- ------------- -------------- ------------
------------ ------------ -----------
5199 15.00 0.00 0.00 0.00 0.00
0.00 5.00
In the actual final display, there are many tables joined to this, so
the resultset really looks like:
CustNo InvNo ItemNo Qty Unit Price IncentiveAmt
TotalIncentive DlrPay SvcMgr PartsDept SvcAdv SvcDept
SvcTech Contest
------------ --------------- ------------- -----------
---------------- --------------------- ----------------------
------------- -------------- ---------------- -------------
-------------- -------------- --------------
07235 54521337 02951 6 54.95 20.00
120.00 15.00 0.00 0.00 0.00
0.00 0.00 5.00
07235 54521337 03111 12 40.95 17.00
204.00 15.00 0.00 0.00 0.00
0.00 0.00 2.00
07235 54521337 01121 24 30.95 20.00
480.00 15.00 0.00 0.00 0.00
0.00 0.00 5.00
07235 54521337 01161 12 36.95 25.00
300.00 15.00 0.00 0.00 0.00
0.00 0.00 10.00
07235 54521337 06011 12 47.95 22.00
264.00 15.00 0.00 0.00 0.00
0.00 0.00 7.00
07235 54521337 10521 12 41.95 19.00
228.00 15.00 0.00 0.00 0.00
0.00 0.00 4.00
I'd really like it to look like this:
CustNo InvNo ItemNo Qty Unit Price IncentiveAmt
TotalIncentive DlrPay Contest
------------ --------------- ------------- -----------
---------------- --------------------- ----------------------
------------- --------------
07235 54521337 02951 6 54.95 20.00
120.00 15.00 5.00
07235 54521337 03111 12 40.95 17.00
204.00 15.00 2.00
07235 54521337 01121 24 30.95 20.00
480.00 15.00 5.00
07235 54521337 01161 12 36.95 25.00
300.00 15.00 10.00
07235 54521337 06011 12 47.95 22.00
264.00 15.00 7.00
07235 54521337 10521 12 41.95 19.00
228.00 15.00 4.00
I'm still debating on displaying this as a listbox or subform on the main form.
I'm thinking if it is in a listbox, simply loop through the last 7
colums. If the entire column is 0 then make that column a 0" width in
the list box.
If I go with a datasheet subform, run a recordset clone and if all
values for a given field are 0 then make that field hidden.
any ideas?
I have to do this on a form and on a report (print out of the form) so
I'm trying to think of a good way to do this that will apply to both.
Thanks,
David