Splitting positive and negative figures into seperate columns (1 Viewer)

CSBLUE · Feb 15, 2012

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!

RainLover · Feb 15, 2012

I am not sure that moving the data is the correct way to go. But then I am not sure of your intentions either.

If you has a Column that described the transaction we could work with that. Something like Credit/Debit of Sale/Purchase.

If you could explain more then it would be easier to help.

Mr. B · Feb 15, 2012

You can create one query but use two columns in your query.
One column would be:

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

The second column would be:

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

Try to copy and paste each of the statements above in to their own column of a query. This will put the positive numbers in the Credits column and the negative numbers in the Debits column. If you want a zero instead of leaving the row blank where the criteria is now met, just change the "" to a zero (0),

CSBLUE · Feb 15, 2012

Many thanks both of you for such speedy replies.

Mr B – That has worked perfectly in splitting the debit and credit transactions into separate columns – thank you.

However, id like to sum the total credits and total debit for each acc. At the moment results are displayed as per the attachedment 1, I would like them totalled as per attachment 2.

I thought it may be just as easy as selecting “SUM” from the drop down menu under the new “Credits” and “Debits” columns, but with this selected the query fails to run.

Any advice would be much appreciated - thank you.

tehNellie · Feb 15, 2012

Code:

Select accountnumber, Sum(credits) As totalCredits, Sum(debits) as totaldebits, Sum(credits) - Sum(debits) as Balance
FROM PreviousQuery
[b]GROUP BY accountnumber[/b]

Groups by the Accountnumber, sums all of the credits and debits into totals and outputs the difference between the two as a "balance" column. Any additional columns, like the date that you want to be in the output, but not calculate need to be included in the GROUP BY statement (the design view will do that for you if you click the "totals" button)

CSBLUE · Feb 15, 2012

tehNellie said:
Code:

Select accountnumber, Sum(credits) As totalCredits, Sum(debits) as totaldebits, Sum(credits) - Sum(debits) as Balance FROM PreviousQuery [B]GROUP BY accountnumber[/B]

Groups by the Accountnumber, sums all of the credits and debits into totals and outputs the difference between the two as a "balance" column. Any additional columns, like the date that you want to be in the output, but not calculate need to be included in the GROUP BY statement (the design view will do that for you if you click the "totals" button)

hmmm not too sure what you mean. despite the code above you metion it can be done in design view? After clicking the "totals" button in the design view it by default put "Group By" in each of the columns. Which should i then change to "sum"?

When I add "sum" to the "Credit" and "Debit" columns i get the following error message when trying to run the query..

"The Microsoft Jet database engine could no exeute the SQL statement because it contains a field that has an invalied data type."

tehNellie · Feb 15, 2012

Change the blank space in the IF statements to a 0

Code:

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

to

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

CSBLUE · Feb 15, 2012

aaaah brilliant !! thank you SO much

Splitting positive and negative figures into seperate columns (1 Viewer)

CSBLUE

New member

RainLover

VIP From a land downunder

Mr. B

"Doctor Access"

CSBLUE

New member

Attachments

tehNellie

Registered User.

CSBLUE

New member

tehNellie

Registered User.

CSBLUE

New member

Similar threads

Users who are viewing this thread