Reports & Calculations

[CleoMan]

Here is my problem. Lets say I have a table called Sales, and in this table I have a column called total , which stores a value for example : -100, 200,-10.

If the value is less than 0 it is a expense and > 0 means its a income.

So now I have a report, and i need to calculate the total expenses(-) and total income(+), from a single column. Does any one have any suggestions?

 

[Kliot]

CleoMan,

I would break it up into two reports one for income and one for expenses. Base the Income report on a query for all records where total is >0 and the Expenses report on totals <0.

hope this helps
Perrin

 

[beanbrain]

You could create a table that has the the name of the transaction, and a debit column as well as a credit column. Then create a scan loop to read from your master table and copy any amount less than zero into the debit column and any amount greater than zero into the credit column of the new table. The report from the new table.

 

[CleoMan]

First of I cant use 2 reports, so no go Perrin, thx anyway.
Second, I considered creating a second table like beanbrain suggested, but here is the problem.

The user enters parameters for a range of results, for which I use SQL to create a answer table. Consider this my main table. This table is then used in the report. The report contains group bands.

So if I do use a second table how would I link it to the specific group band in the report and another problem with reports is that when I use a second table in a report the report manager only allows me use aggregate functions on the fields(COunt,Sum,Min, Max etc) of the second table

 

[Langley]

Just add a field to your table, call it TransType or something and make it a Short Int. Run a query like this to set the transaction type:

Query
ANSWER: :PRIV:ANSWER.DB

Test.DB | Amount | TransType  |
        | >0     | changeto 1 |
        | <0     | changeto 2 |

EndQuery

Then query as usual for your report, including the new TransType field. Add a group band to the report based on TransType and do a total for each kind of transaction at the bottom of the group band.

Hope this helps.




Mac

&quot;There are only 10 kinds of people in this world... those who understand binary and those who don't&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[jartman2]

CleoMan:

Make the expense and income calculated fields, then define them thusly:

income = sum(iif([Sales.total] > 0,[Sales.total],0))
expense = sum(iif([Sales.total] < 0,[Sales.total],0))

- John