User Profile

svjensen · Feb 1 '11, 05:11 PM

Yes! Got it!

Code:

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

...

svjensen · Feb 1 '11, 05:01 PM

Ok, I got the following to work:

Created a cross join query named qryCrossJoin:

Code:

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

And used that in my cross tab query:

Code:

TRANSFORM Sum(tblExpenses.Value) AS SumOfValue
SELECT qryCrossJoin.ExpenseArea, qryCrossJoin.ExpenseType

...

svjensen · Feb 1 '11, 04:47 PM

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?

svjensen · Feb 1 '11, 04:44 PM

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

svjensen · Feb 1 '11, 04:16 PM

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

User Profile

Profile Sidebar

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

Leave a comment:

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