By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,358 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

Splitting Positive and Negative Figures into Separate Columns

P: 2
Hi, hoping someone can help…

I have a table called “Table1” and within it are various columns including a “Transactions” column that contain both credit and debit (positive and negative) transactions. As well as the “Transactions” column in “Table1” there is also an “Account Number” column. There are several account numbers and each account number has several transactions.

The end goal is to create a query that splits the debit/credit transactions into separate columns and then totalled for each account.

Iv tried creating one query with <0 criteria under the “Transactions” field for negative and vice versa for positive then a third to bring the two columns together but it’s not really working!

Ideally it would be great to do from one query but think it may be unlikely to do from the query ‘design view’.

Whilst I have no experience with SQL, I am confident of following any clear guidance offered.

I thank you in advance!
Feb 15 '12 #1

✓ answered by Rabbit

You can also use the IIF function to split your column without multiple queries.

But if you want to continue down the multiple queries, create a positve query and a negative query, then union them. Each will require a dummy field.

Share this Question
Share on Google+
3 Replies


Narender Sagar
100+
P: 189
If your table also has one of field something like "Transaction_Type", e.g. for every negative transaction "DR" and for positive transaction "CR", then you can create "Crosstab Query" and keep above field in columns & other required fields in Rows. Then I think you'll get result the way you want it.
Feb 15 '12 #2

Rabbit
Expert Mod 10K+
P: 12,366
You can also use the IIF function to split your column without multiple queries.

But if you want to continue down the multiple queries, create a positve query and a negative query, then union them. Each will require a dummy field.
Feb 15 '12 #3

P: 2
thanks guys, really appreciate it.

Iv cracked it with the IF staments. Created two new columns, one column:

Credits: IIf(Table1.Transactions > 0, Table1.Transaction,0)

The second column:

Debits: IIf(Table1.Transactions < 0, Table1.Transaction,0)


Thanks again.
Feb 15 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.