Darryl Collins
darryl at whittleconsulting.com.au
Wed Jul 31 18:52:50 CDT 2013
Hi Charlotte, Aaaah, It is not the top totals that are the problem (I don't even bother with them in Access, they are done in Excel). It is the LHS totals. In the SQL query I put them as the first column on the far left, where they sit nicely and perform flawlessly until I open the query in design mode. Even if I close the Query without saving Access will move the Row Totals Column to the left and screw everything up. I have to REOPEN the query in design mode, move it BACK to the far LHS, save and close. Then it will sit there until I need to open the query in Design mode again. Everytime... grrrrr.. Painful..... -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust Sent: Thursday, 1 August 2013 2:05 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] A2010 x-tab queries and LHS totals - grrrr!! Feeling a little better now that's off your chest? ;-) If you add a query totalling the values to the report source or tally the values in code, you can put the totals at the top. If I want to do this, I usually build a subreport based on a totals query and put it in the report header. It makes perfect programming sense to have them at the bottom because the entire report has to be run to generate them. Calculating them first would require running the underlying queries multiple times and is a quantum increase in complexity and could lead to the dreaded "query is too complex" error. Charlotte On Thu, Jul 25, 2013 at 6:43 PM, Darryl Collins < darryl at whittleconsulting.com.au> wrote: > Hi All, > > Is it just me, a bug or something Access has always done? Whatever it > is it is really starting to piss me off. > > Scenario: > > I have a whole stack of X-tab queries (100+ of the buggers) where I > have the total quantity being x-tabbed as both a value (as the xtab > part of the > query) and also as a row heading so I can get a total per each row. > And I want the damn total where it belongs (at least in my opinion) > and that is on the LHS of the data. > > Why? Because this data gets exported to Excel where I always make my > reports with totals on the Left and Top. Unlike the crazy fashion of > the last 200 years of bottom and right, which is helpful to, ummmm... nobody. > The Romans used the phrase "To Add Up" as even back then they knew > the only sensible and logical place for the totals is at the Top where > it is always visible. We still say "add up" but most folks these days > "add down". Feh, Bah - humbug to that I say. > > Putting the totals top and left means: > > 1: they are always in the same cells - useful for consistency and > audit and general sanity. > 2: they are always visible at a glance and you don't have to hunt for them. > 3: it make using those totals else-where in Excel simple, rather than > it being a complicated exercise in vba or formulae to find them. > > Just to list a few good reason a normal and sane individual might like > to do this. So why for the love of God does Access insist on 'automagically' > (and randomly it seems) changing my queries to show the total on the RHS. > I move the totals column back yet again to the far LHS of the query, > save the query, run the query, and all is good - totals on LHS as > saved. Should be 'end of story', but no.... > > Everytime I edit the damn query Access will reposition it again, which > means I have open the query again, move it again, save it again. What > a right Pain in Ar$e. Damnit MSoft. When I save the query in a > certain layout, I want it like that. Not what some B grade coding > Hack on a summer internship thinks I want. Grrrrrr!! Bugger off, > when I save it - leave it alone! > > Very open to ideas and suggestions here folks as this one is really > annoying me now.... > > Sheeesh, and they have just release SP2 for Office 2010. > > > > > > Darryl Collins > Whittle Consulting Pty Ltd > Suite 8, 660 Canterbury Rd > Surrey Hills, VIC, 3127 > > p: +61 3 9898 3242 > m: +61 418 381 548 > f: +61 3 9898 1855 > e: > darryl at whittleconsulting.com.au<mailto:darryl at whittleconsulting.com.au > > > w: www.whittleconsulting.com.au<http://www.whittleconsulting.com.au/> > > -- > 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