Combine two crosstab queries

[Ray67]

Hello

I'm trying to combine two crosstab queries but when i set the join properties the data doesn't look right.

Query One
TRANSFORM Sum(Q_TransactionsbyCategory1.WithdrawalAmount) AS SumOfWithdrawalAmount
SELECT Q_TransactionsbyCategory1.TransactionDate, Q_TransactionsbyCategory1.Description, Q_TransactionsbyCategory1.TransactionType, Q_TransactionsbyCategory1.CheckNumber, Q_TransactionsbyCategory1.Deposit, Q_TransactionsbyCategory1.Debit
FROM Q_TransactionsbyCategory1
GROUP BY Q_TransactionsbyCategory1.TransactionDate, Q_TransactionsbyCategory1.Description, Q_TransactionsbyCategory1.TransactionType, Q_TransactionsbyCategory1.CheckNumber, Q_TransactionsbyCategory1.Deposit, Q_TransactionsbyCategory1.Debit
PIVOT Q_TransactionsbyCategory1.CategoryName;

Query Two

TRANSFORM Sum(Q_TransactionsbyCategory1.DepositAmount) AS SumOfDepositAmount
SELECT Q_TransactionsbyCategory1.TransactionDate, Q_TransactionsbyCategory1.Description, Q_TransactionsbyCategory1.TransactionType, Q_TransactionsbyCategory1.CheckNumber, Q_TransactionsbyCategory1.Deposit, Q_TransactionsbyCategory1.Debit
FROM Q_TransactionsbyCategory1
GROUP BY Q_TransactionsbyCategory1.TransactionDate, Q_TransactionsbyCategory1.Description, Q_TransactionsbyCategory1.TransactionType, Q_TransactionsbyCategory1.CheckNumber, Q_TransactionsbyCategory1.Deposit, Q_TransactionsbyCategory1.Debit
PIVOT Q_TransactionsbyCategory1.CategoryName;


Thank you

[rpeare]

You'll have to be more specific. You're using the same table for both queries is there a reason you're not just creating one query to do it?.

I'm assuming you want a list of all withdrawls and deposits in a singular list and it looks like you have the same columns in the same order so have you tried a UNION query of the two crosstabs (if you don't want to create one crosstab). I'm not sure a union of crosstabs would work but you could try

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2

[Ray67]

yes i want withdrawls and deposits in a singular list. I try crosstabs query but you can only put one value.


This how the table look

Transaction Date	Category	Description	TransactionType	Check Number	Deposit Amount	Withdrawal Amount
01-Oct-11	Waste	Choice Waste	Check	2244	0.00	225.65
03-Oct-11	Rent	Rent Apt# 3	Check		775.00	0.00
03-Oct-11	Rent	Rent Apt# 2	Check		775.00	0.00
13-Oct-11	Postage	Post office Certified Mail & Stamps	Debit Card		0.00	3.2

Now this is how i want the data to look like.

Transaction Date	Description	TransactionType	Check Number	Deposit Amount	Withdrawal Amount	Postage	Rent	Waste
01-Sep-11	Post office Certified Mail & Stamps	Debit Card		0.00	3.29	3.29
01-Oct-11	Choice Waste	Check	2244	0.00	225.65			225.65
03-Oct-11	Rent Apt# 2	Check		775.00	0.00		775.00
03-Oct-11	Rent Apt# 3	Check		775.00	0.00		775.00