Splitting Positive and Negative Figures into Separate Columns

[Giles Marriott]

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!

[Narender Sagar]

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.

[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.

[Giles Marriott]

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.