[AccessD] Return all values from crosstab

Hi Stuart,

Thanks for the pointer.

I'll have a play and see where I end up ;-)

Chris S

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Stuart
> McLachlan
> Sent: 20 May 2004 01:48
> To: Access Developers discussion and problemsolving
> Subject: Re: [AccessD] Return all values from crosstab
> 
> 
> On 19 May 2004 at 21:26, Chris Swann wrote:
> 
> > 
> > However, as the data is run against other areas, there are gaps 
> in the data
> > so I cannot use the whole crosstab export approach. I will 
> never know which
> > items may be present as the data changes on a weekly basis.
> > 
> <snip>
> > Is there any way to make the crosstab output all values even if 
> there is no
> > data to return ? So the third example above would look like this
> > 
> > Area2	type1a data data data data <<<< forced to return this line
> > Area2	type1b data data data data <<<< forced to return this line
> > Area2	type2a data data data data <<<< only data available
> > Area2	type2b data data data data <<<< only data available
> > Area2	type3a data data data data <<<< forced to return this line
> > Area2	type3b data data data data <<<< forced to return this line
> > 
> 
> You need a couple of  tables which contain lists of all Areas and all 
> Types. You use this to build a list of all the possible Area/Type 
> combinations and left join your data to this "superlist", 
> 
> Something like:
> 
> SELECT AreaTypes.AreaName, AreaTypes.RecordType, Sum(tValues.data1) 
> AS SumOfdata1, Sum(tValues.data2) AS SumOfdata2, Sum(tValues.data3) 
> AS SumOfdata3, Sum(tValues.data4) AS SumOfdata4
> FROM
> (SELECT tAreas.AreaName, tTypes.RecordType FROM tAreas, tTypes) as 
> AreaTypes
> LEFT JOIN tValues ON (AreaTypes.RecordType = tValues.RecordType) AND 
> (AreaTypes.AreaName = tValues.Location)
> GROUP BY AreaTypes.AreaName, AreaTypes.RecordType
> 
>  
> -- 
> Lexacorp Ltd
> http://www.lexacorp.com.pg
> Information Technology Consultancy, Software Development,System 
> Support.
> 
> 
> 
> -- 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com