Cross tab - force it to show row when no value

[svjensen]

I have a table structured like this:

tblExpenses
===========
uid autonumber (key)
ExpenseArea text
ExpenseType text
Year Number
Value Number

With the following posts:

uid ExpenseArea ExpenseType Year Value
=== =========== =========== ==== =====
001 Gynger Investering 2010 15
002 Gynger Investering 2011 10
003 Gynger Investering 2012 8
004 Gynger Investering 2013 12
005 Gynger Investering 2014 13
006 Gynger Drift 2010 2
007 Gynger Drift 2011 2
008 Gynger Drift 2012 3
009 Gynger Drift 2013 2
010 Gynger Drift 2014 3
011 Karuseller Drift 2010 12
012 Karuseller Drift 2011 12
013 Karuseller Drift 2012 13
014 Karuseller Drift 2013 12
015 Karuseller Drift 2014 13

Using the below query I get the data summed for each year.

TRANSFORM Sum(tblExpenses.Value) AS SumOfValue
SELECT tblExpenses.[ExpenseArea], tblExpenses.[ExpenseType]
FROM tblExpenses
GROUP BY tblExpenses.[ExpenseArea], tblExpenses.[ExpenseType]
PIVOT tblExpenses.[Year];


Now, As you can see in the dataset, there are no values for 'Karuseller', 'Investering', and my question is, if there is some way of forcing the query to return a row for this but without any values?

Or would I need to do this in the form/report, that I will use to present the data?

/Soren

 

[DCrake]

In the column properties in the crosstab query type in the column headings in there in the ordr you want them to appear in the list. This way column that do not contain data are still visible hen launched.

 

[svjensen]

I think maybe I have been unclear in descriping my problem.

I am happy with the columns being show, but I would like to force Access to write a row even if there are no values.

In my initial example I would get the following:
ExpenseArea ExpenseType 2010 2011 2012 2013 2014
========= ========== ==== ==== ==== ==== ====
Gynger Investering 15 10 8 12 13
Gynger Drift 2 2 3 2 3
Karuseller Drift 12 12 13 12 13


What I would like is the above, but with an additional row (values could be blanks, zero or null):
Karuseller Investeringer 0 0 0 0 0

I hope that makes it clearer