Help with Cross tab query

[Don Sealer]

How would I make this query sum the fields "expense"+"cash"

TRANSFORM Sum(Expenses.Expense) AS SumOfExpense
SELECT Expenses.Debtors, Expenses.Cash, Sum(Expenses.Expense) AS [Total
Of expense]
FROM Expenses
GROUP BY Expenses.Debtors, Expenses.Cash
PIVOT Format([date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

I can't figure this out and I feel pretty dumb about it.
Thanks again,
Don..............

[jimfortune]

Don Sealer wrote:

How would I make this query sum the fields "expense"+"cash"

TRANSFORM Sum(Expenses.Expense) AS SumOfExpense
SELECT Expenses.Debtors, Expenses.Cash, Sum(Expenses.Expense) AS [Total
Of expense]
FROM Expenses
GROUP BY Expenses.Debtors, Expenses.Cash
PIVOT Format([date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

I can't figure this out and I feel pretty dumb about it.
Thanks again,
Don..............

After changing [Date] to [ExpenseDate],

Expenses:

ExpenseID Expense Cash Debtors ExpenseDate
1 $100 $20 Debtors1 1/1/05
2 $200 $40 Debtors2 1/2/05
3 $300 $60 Debtors3 2/2/05
4 $400 $80 Debtors4 2/5/05
5 $500 $100 Debtors5 3/1/05

Expenses_Crosstab:
TRANSFORM Sum(Expense) + Sum(Cash) AS TheValue SELECT Debtors,
Sum(Cash) AS [TotalCash], Sum(Expense) As [TotalExpense], Sum(Expense)
+ Sum(Cash) AS ExpensePlusCash FROM Expenses GROUP BY Debtors, Cash,
Expense PIVOT Format([ExpenseDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

!Expenses_Crosstab:

Debtors TotalCash TotalExpense ExpensePlusCash Jan Feb ...
Debtors1 $20 $120 $120 $120
Debtors2 $40 $200 $240 $240
Debtors3 $60 $300 $360 $360
Debtors4 $80 $400 $80 $480
Debtors5 $100 $500 $600 $600

I'm not sure that's what you're looking for. I liked the way the "In"
part handled Null dates and ensured that all months show up. I'm
learning from you. Thanks.

James A. Fortune

[Don Sealer]

Thanks for your reply. I'd like to take credit for the "in" but it was
given to me by someone.
As for the suggestion and SQL you gave me. I put the SQL into my query
but it doesn't give the results I was looking for. I worked with what
you gave me and came up with this. As far as I can tell it gives me the
exact results I was looking for. Only took minor alterations to what
you suggested (just took me awhile to figure it out). You put me on the
right track, thanks very much.
Don..........

TRANSFORM Sum(Expenses.Expense) AS SumOfExpense
SELECT Expenses.Debtors, Sum(Expenses.Cash) AS TotalCash,
Sum(Expenses.Expense) AS TotalExpense, Sum([Expense])+Sum([Cash]) AS
ExpensePlusCash
FROM Expenses
WHERE (((Expenses.Debtors) Is Not Null))
GROUP BY Expenses.Debtors
PIVOT Format([ExpenseDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");


ji********@compumarc.com wrote:

Don Sealer wrote:

How would I make this query sum the fields "expense"+"cash"

TRANSFORM Sum(Expenses.Expense) AS SumOfExpense
SELECT Expenses.Debtors, Expenses.Cash, Sum(Expenses.Expense) AS [Total
Of expense]
FROM Expenses
GROUP BY Expenses.Debtors, Expenses.Cash
PIVOT Format([date],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug" ,"Sep","Oct","Nov","Dec");

I can't figure this out and I feel pretty dumb about it.
Thanks again,
Don..............

After changing [Date] to [ExpenseDate],

Expenses:

ExpenseID Expense Cash Debtors ExpenseDate
1 $100 $20 Debtors1 1/1/05
2 $200 $40 Debtors2 1/2/05
3 $300 $60 Debtors3 2/2/05
4 $400 $80 Debtors4 2/5/05
5 $500 $100 Debtors5 3/1/05

Expenses_Crosstab:
TRANSFORM Sum(Expense) + Sum(Cash) AS TheValue SELECT Debtors,
Sum(Cash) AS [TotalCash], Sum(Expense) As [TotalExpense], Sum(Expense)
+ Sum(Cash) AS ExpensePlusCash FROM Expenses GROUP BY Debtors, Cash,
Expense PIVOT Format([ExpenseDate],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

!Expenses_Crosstab:

Debtors TotalCash TotalExpense ExpensePlusCash Jan Feb ...
Debtors1 $20 $120 $120 $120
Debtors2 $40 $200 $240 $240
Debtors3 $60 $300 $360 $360
Debtors4 $80 $400 $80 $480
Debtors5 $100 $500 $600 $600

I'm not sure that's what you're looking for. I liked the way the "In"
part handled Null dates and ensured that all months show up. I'm
learning from you. Thanks.

James A. Fortune