Union Query Duplicates data

[Ray67]

Union query duplicates data. What i'm i doing wrong????


SELECT T_Transactions.TransactionDate, T_Transactions.Description, T_Transactions.TransactionType, T_Transactions.CheckNumber, T_Transactions.Category, "DepositAmount" AS TypeofTransaction,DepositAmount AS Data FROM T_Transactions
WHERE [DepositAmount] Is Not Null;
UNION ALL SELECT T_Transactions.TransactionDate, T_Transactions.Description, T_Transactions.TransactionType, T_Transactions.CheckNumber, T_Transactions.Category, "WithdrawalAmount" AS TypeofTransaction,WithdrawalAmount AS Data FROM T_Transactions
WHERE [WithdrawalAmount] Is Not Null;

[alansidman]

Change from Union All to Union. This will eliminate duplicates.

[Ray67]

Alansidman

Thank you for your reply. I changed Union all to Union and i get this messages Invalid SQL Statement; expected 'Delete','Insert','procedure','Select', or 'Update'.
I changed the SQL to join propeties with other tables here is the new sql


SELECT T_Transactions.TransactionID, T_Transactions.TransactionDate, T_Description.Description, T_TransactionType.TransactionType, T_Transactions.CheckNumber, T_Transactions.DepositAmount AS Deposit, T_Transactions.WithdrawalAmount AS Withdrawal, T_CategoryName.CategoryName, "DepositAmount" AS TypeofTransaction, T_Transactions.DepositAmount AS Data
FROM T_TransactionType INNER JOIN (T_Description INNER JOIN (T_CategoryName INNER JOIN T_Transactions ON T_CategoryName.CategoryID = T_Transactions.Category) ON T_Description.DescriptionID = T_Transactions.Description) ON T_TransactionType.TransactionTypeID = T_Transactions.TransactionType;
UNION Select T_Transactions.TransactionID, T_Transactions.TransactionDate, T_Description.Description, T_TransactionType.TransactionType, T_Transactions.CheckNumber, T_Transactions.DepositAmount AS Deposit, T_Transactions.WithdrawalAmount AS Withdrawal, T_CategoryName.CategoryName, "WithdrawalAmount" AS TypeofTransaction, T_Transactions.WithdrawalAmount AS Data FROM T_TransactionType INNER JOIN (T_Description INNER JOIN (T_CategoryName INNER JOIN T_Transactions ON T_CategoryName.CategoryID = T_Transactions.Category) ON T_Description.DescriptionID = T_Transactions.Description) ON T_TransactionType.TransactionTypeID = T_Transactions.TransactionType;

Here is how the data looks like

TransactionID	TransactionDate	Description	TransactionType	CheckNumber	Deposit	Withdrawal	CategoryName	TypeofTransaction	Data
2	01-Oct-11	Other	Check	2244	0.00	225.65	Waste	DepositAmount	0.00
2	01-Oct-11	Other	Check	2244	0.00	225.65	Waste	WithdrawalAmount	225.6