Charlotte Foust
charlotte.foust at gmail.com
Fri Oct 1 10:33:09 CDT 2010
Hiding a column that contains all zero values may seem like a good idea, but wouldn't it be confusing to users if a column sometimes appeared and sometimes vanished? Charlotte Foust On Thu, Sep 30, 2010 at 5:31 PM, David McAfee <davidmcafee at gmail.com> wrote: > 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 > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >