Stephen Bond
stephen at bondsoftware.co.nz
Thu Sep 26 15:03:10 CDT 2013
Brad, I did this exercise a few years ago. I seem to remember building a cross-tab query in the UI then copying and pasting the SQL from the query into a code module and manipulating the code. Basically with eight Parts categories in real life you could hard-wire them into the SQL code but I chose to dynamically load the column headings into the report with the values as they were in the Parts category master table. This means you can add a category to the master table and the code looks after it (up to a predetermined maximum, determined by page-width). The load of each text box in the detail line in the report then has to be coded as well. Providing you label the text-boxes on the report meaningfully (txtheader0, txtheader1 ... txtdetail0, txtdetail1 ... :-\ ) this part of the code is not much more than a 3 line loop. It was slow nutting this out but now I use this code frequently. Will dig it up if it sounds helpful ... Stephen Bond -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks Sent: Friday, 27 September 2013 7:46 a.m. To: Stephen Subject: Re: [AccessD] Access Report Sub-Totals - Need to Display Differently Rocky, There is a fixed number of Parts Categories. Eight of them in real life. I only showed 3 for my example to keep it simple. Also, there are about 15 other fields that are on the report that I did not show in the example. These all need to be shown at the sub-total level, also. Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin Sent: Thursday, September 26, 2013 2:28 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Report Sub-Totals - Need to Display Differently Are the parts categories fixed to those three, or are there a variable number of categories? R -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks Sent: Thursday, September 26, 2013 11:47 AM To: Access Developers discussion and problem solving Subject: [AccessD] Access Report Sub-Totals - Need to Display Differently All, I have an existing Access 2007 Report which shows sub-totals for "Part Categories" like this. (Detail records are not shown) ______________________________________ Date Part-Category Nbr-of-Parts 9/1/2013 Rings 10 9/1/2013 Pins 50 9/1/2013 Medallions 25 ______________________________________ Recently there has been a request to display this data on the report in a different fashion, with the "Parts-Category" being shown in the heading like this and then have only one line for each date. ______________________________________ Date Rings Pins Medallions 9/1/2013 10 50 25 ______________________________________ I would like to use Access reporting "Built-in" features to do this. Any ideas/advice? Thanks, Brad -- 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 -- This message was scanned by ESVA and is believed to be clean. Click here to report this message as spam. http://h0stname/cgi-bin/learn-msg.cgi?id=2A1A228CAB.5A64A -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com