How to force Cross tab to show row when no value?

[svjensen]

Let us say, that 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?

One thought I had was to have the expensetypes listed in a separate table and using a nested SQL (or something along those lines) to force it to show all expensetypes as described.


/Soren

[Rabbit]

You could either cross join an expensearea table with an expensetype table and then outer join that to expenses table or you could create a table with all the combinations you need and then outer join that with the expenses table.

[parodux]

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

er der andet, så kan du fange mig her!~)

[svjensen]

Parodux,

(will write in English in order for others to be able to read along)
I tried your suggestion, but it still does not give me a row for 'Karuseller', 'Investering'.
Could I be missing something?

/Søren

[Rabbit]

He misunderstood what you wanted to do. You have to use my suggestion.

[svjensen]

I was actually trying to get that to work.
I created the two additional tables (ExpensesArea and ExpensesType), and added to id fields to the Expenses table.

I can get it to handle one of them by using outer join, but it will not accept two. It suggests that I first do one query with one join, and then use that query for the next join.
Is that what you mean by cross join?

And could you perhaps give an example?

[svjensen]

Ok, I believe that I get the cross join part now:
SELECT * FROM tblExpenseAreas , tblExpenseTypes

But can I use that directly in the other query? Or do I need to save it as a separate qeury, and then use that like a table?

[Rabbit]

You can do it all in one query by making that into a subquery but it might be easier for you to understand if you save it into a separate query. Whichever way you decide to do it, next you'll want to left or right outer join (depending on how you order the tables) that subquery or query.

[svjensen]

Ok, I got the following to work:

Created a cross join query named qryCrossJoin:

SELECT tblExpenseAreas.ExpenseArea, tblExpenseTypes.ExpenseType, tblExpenseAreas.uid AS AreaId, tblExpenseTypes.uid AS TypeId
FROM tblExpenseAreas, tblExpenseTypes;

And used that in my cross tab query:

TRANSFORM Sum(tblExpenses.Value) AS SumOfValue
SELECT qryCrossJoin.ExpenseArea, qryCrossJoin.ExpenseType
FROM qryCrossJoin LEFT JOIN tblExpenses ON (qryCrossJoin.TypeId = tblExpenses.TypeId) AND (qryCrossJoin.AreaId = tblExpenses.AreaId)
GROUP BY qryCrossJoin.ExpenseArea, qryCrossJoin.ExpenseType
PIVOT tblExpenses.Year;

As mentioned this works :-)


Now, the example above is a lot simpler than my actual queries, which combines a number of different tables.
So I would really like to learn how to achieve the above in a single query.
Care to help?

[svjensen]

Yes! Got it!

TRANSFORM Sum(tblExpenses.Value) AS SumOfValue
SELECT Q.ExpenseArea, Q.ExpenseType
FROM (SELECT tblExpenseAreas.ExpenseArea, tblExpenseTypes.ExpenseType, tblExpenseAreas.uid AS AreaId, tblExpenseTypes.uid AS TypeId
FROM tblExpenseAreas, tblExpenseTypes) As Q LEFT JOIN tblExpenses ON (Q.AreaId=tblExpenses.AreaId) AND (Q.TypeId=tblExpenses.TypeId)
GROUP BY Q.ExpenseArea, Q.ExpenseType
PIVOT tblExpenses.Year;

I see a brand new world unfolding before me ;-)

Thanks a bunch, Rabbit!

[Rabbit]

No problem, good luck with the rest of the project!