Calculation Problems

[rhonda1111]

Hello,

I am brand new to all of this, and hoping someone can help

I'm trying to calculate net income - my data is stored on a table as follows:

Account
Amount
Date

I then have the data linked to an Account table which also identifies whether the Account is an Income or Expense Account.
I then wrote a crosstab query to sum my data by year.

Now, I need to be able to subtract the Expenses from the Income by year on my report, and am having major difficulties.

Does this make any sense to anyone??? Any help you can provide would be so very appreciated!!

Thought I should add, I have been an Access user for a number of years, but not on a regular basis. I have muddled through on quite a few projects, but I am stumped on this. Please help, I'm desperate... Did I provide enough information in my question? I can provide whatever additional information might help!! Thank you!!

 

[Ranman256]

could you make a UNION query,
select * from qsExpenses
UNION
select * from qsIncome

make the expense query subtract values for each ACCT# (negatives)
and the Income query add all values.

then use the qnUnion query and add those values for a total net.

 

[rhonda1111]

Hi,

Thank you so much for your reply. So, do I need separate tables for income and expenses in order to do this?

It seems like I am missing something very basic, maybe the way I have my data structured.

Thanks again!!

 

[Pat Hartman]

No. The technique depends on how the data is stored. If your Income/Expense account amounts have natural signs, you can simply sum them to get the net. If everything is stored as a positive value, you need to multiply one side or the other by -1 so you can net the amounts.

The key is do the netting PRIOR to the crosstab. If you do it after the fact, you have a lot of columns to deal with and those columns could change over time.

 

[rhonda1111]

Hello,

Thank you so much for your reply!! It does make sense to store the expenses as a negative and then do a sum. That helped tremendously. I am now able to calculate the net income by summing the amounts.

Now, I am trying to show my numbers as positives rather than negatives in my report.

I was reading about the absolute value function, but what would that look like with my formula:

=Sum([2017])

Thank you so much again!!

 

[rhonda1111]

Hello again,

I actually couldn't use absolute value because it turned some of my negatives to a positive number that needed to stay negative... ugh

So, now I wrote another cross tab query, and linked the two cross tabs together in another query so that I have both the positive numbers for the report, and the negative numbers to use in my calculation.

Thank you again so much!!

 

[CJ_London]

Not sure if this is relevant to your needs, you can use the formatting property for a control to display numbers how you want without affecting the underlying data.

For numbers the formatting property has four sections

positive;negative;zero;null

so for example with a format of

0;[Red]0

value...displays
1.........1
-2........2

or you might use 0;(0)

which displays

value...displays
1.........1
-2........(2)

if you don't want to display zeros, use 0;[Red]0;"" or 0;[Red]0;[White]

and perhaps for nulls 0;[Red]0;[White];"N/A"

you can choose from a number of colours - think they are red, blue, green, yellow, magenta, black, white

formatting nulls can be useful to send a message to a user when importing data - e.g

;;;"You must enter a number here"

text has two formatting options - value;null

so you can use

@;"Please enter your name"

as a prompt to a user

 

[Pat Hartman]

To make the sum() an absolute value, the abs() function goes outside the sum.

=abs(sum([2017]))

I always use natural signs. That allows my transactions to be credits or debits when they are adjustments.