David McAfee
davidmcafee at gmail.com
Fri Oct 1 12:02:18 CDT 2010
Normally I would say yes. But... in the case, I would say no. I've converted a Excel type entry system where our sales managers email excel sheets to our Accounting dept. There are different rules/splits on every Excel sheet depending on a given dealership and sales manager. A certain dealership may only have incentives for the Service Dept and Parts Dept while another dealership might have certain sales incentives for the Service Manager, Service Advisor and technician. I am trying to make the report/form show the same fields/columns that are on the Excel sheet so it is easier to match up to the Excel sheets for verification/approval. D On Fri, Oct 1, 2010 at 8:33 AM, Charlotte Foust <charlotte.foust at gmail.com> wrote: > 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 >> > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com >