Darryl Collins
darryl at whittleconsulting.com.au
Thu Sep 26 20:10:33 CDT 2013
" Some care will need to be taken on how you set up your data in Access" I should mention you won't need to change your data table at all, you can hook an Excel Pivot table into a Query without issue. So make the underlying query in Access first with the data you want and then hook the Excel PT off that. This also give you the ability to 'filter' the data to make it more manageable, say, only limit the PT to the UserID records or a certain region etc. Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins Sent: Friday, 27 September 2013 11:01 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access Report Sub-Totals - Need to Display Differently Hi Stuart, Another idea is to hook Excel into the Access source table and use an Excel Pivot table to produce the report instead. Some care will need to be taken on how you set up your data in Access and I am assuming you have access to Office 2007 or greater. Excel 2003 or less will only allow for 256 columns in a Pivot table (or indeed any data) Excel 2007+ can handle 16,384 columns in a Pivot - although the true performance will be limited by available memory and resources. Worth a look anyway, Cheers Darryl -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters Sent: Friday, 27 September 2013 10:39 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Access Report Sub-Totals - Need to Display Differently Hi Stuart, SQL Server tables will let you use tables with over 1000 columns. Perhaps you could link to those tables? Good Luck! Dan -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan Sent: Thursday, September 26, 2013 4:38 PM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access Report Sub-Totals - Need to Display Differently I've just run into a problem creating a large crosstab and outputting to Excel. I developed an application foir recording and analysing market research surveys. One output is an Excel sheet derived from a crosstab with the questions as columns and individual responses as rows. The latest survey has over 256 questions and Access won't create a crosstab with more than 256 columns. :-( I'm currently workng on VBA cod to generate multiple crosstabs with a maximum of 250 columns in each and writing the combined sets out to a tab delimited file. (It gets a bit tricky because there is not necessarily a one-to-one relationship between the rows in the various crosstabs) -- Stuart On 26 Sep 2013 at 15:52, Gary Kjos wrote: > Thanks Brad. I appreciate your fishing results query sample. I do > mostly one off stuff and I use cross tabs a lot. I will sometimes have > several of them run as subqueries to a main query that links the > pieces together for the final presentation to the user which I > generally do via an Excel output file. > > Good luck. > > GK > > > On Thu, Sep 26, 2013 at 2:39 PM, Brad Marks <BradM at blackforestltd.com>wrote: > > > Gary, > > > > Just for you... > > ______________________________________ > > > > Date Fish-Species Nbr-of-Fish > > > > 9/30/2013 Walleye 06 > > 9/30/2013 Northern 15 > > 9/30/2013 Lake Trout 11 > > 9/30/2013 Muskie 01 > > > > ______________________________________ > > > > > > Thanks for the tip on the cross tab query. I haven't worked with > > these much. Only some preliminary experimenting a couple years ago. > > > > Brad > > > > > > > > > > -----Original Message----- > > From: accessd-bounces at databaseadvisors.com > > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gary Kjos > > Sent: Thursday, September 26, 2013 2:30 PM > > To: Access Developers discussion and problem solving > > Subject: Re: [AccessD] Access Report Sub-Totals - Need to Display > > Differently > > > > Well a cross tab query does that. But not sure how that works with a > > report since the columns are dynamic based on the data. > > > > > > On Thu, Sep 26, 2013 at 1:47 PM, Brad Marks > > <BradM at blackforestltd.com>wrote: > > > > > 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 > > > > > > > > > > > -- > > Gary Kjos > > garykjos at gmail.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=DAA9D28C9A.BF06C > > > > > > > > -- > > AccessD mailing list > > AccessD at databaseadvisors.com > > http://databaseadvisors.com/mailman/listinfo/accessd > > Website: http://www.databaseadvisors.com > > > > > > -- > Gary Kjos > garykjos at gmail.com > -- > 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 -- 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