If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Query
I would like to enhance a crosstab query I am working with.
First, I have a tabe of transactions. Each transaction has a date, a trans type, and an amount. In related table of trans types, a boolean descriptor is defined to indicate if the trans amount needs to be negated for calculations. My current crosstab query results look like this: Year Fund SumOfAmount TranType1 TranType2 Etc... 2001 11 5,000.00 2,000.00 -7,000.00 2002 11 3,000.00 5,000.00 -2,000.00 So my question is this... For each year there is a final balance (e.g. 2000, 5000 .) That balance needs to be carried over to the next year for a correct end of year balance. I would like to be able to this in a query, but I don't know how to display and/or use the value. Is this possible? This is the current SQL: TRANSFORM Sum(IIf([TransCatNegate],[TransAmount]*-1,[TransAmount])) AS Amount SELECT Transactions.TransBeginBudgetFY, Transactions.TransRFundtypeID, Sum(IIf([TransCatNegate],[TransAmount]*-1,[TransAmount])) AS SumOfAmount FROM (TransactionCategories INNER JOIN Transactions ON TransactionCategories.TransCatID = Transactions.TransRTransCatID) INNER JOIN TransCategoriesOfInterest ON TransactionCategories.TransCatID = TransCategoriesOfInterest.UseTransCatID WHERE (((Transactions.TransRFundtypeID)=11) AND ((Transactions.TransRRptCatID) Like "345")) GROUP BY Transactions.TransBeginBudgetFY, Transactions.TransRFundtypeID ORDER BY Transactions.TransBeginBudgetFY PIVOT TransactionCategories.TransCategory; |
Thread Tools | |
Display Modes | |
|
|