[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.