[AccessD] Access Report Sub-Totals - Need to Display Differently

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



More information about the AccessD mailing list