Calculations between different columns

[Guest]

Hi,

I've probably an easy problem but I can't really find a solution on my own.

Question:
I have made a table like this:
column1 column2 column3 column4
2 6 4 -
3 12 8 -
4 2 5

What I'm trying to do is a calculation in column4 like this ((column1 +
column2)-column3).
Which should result in resp.
Column4
4
7
1

Can I put a calculation in the column4 properties or do I have to use a query?

I thought this couldn't be hard but I really can't find the solution:S

I would be very gratefull for replies!
Thanks in advance,

greetings Onne

 

[Ed Warren]

Column 4 is a calculated column, therefore should not be stored in the
database. So your table should only contain cols 1..3.
to get column 4 you use a query.

Select column1, column2, column3, (column1+column2-column3) as Column4
From Table

If there is any chance at all that any of columns 1,2,3 could be null then
you will want to add the function nz(columnX,0) in the appropriate place to
make that column = 0.

This way when the values in the columns change, the values in the query are
still correct.
Ed Warren.

 

[Guest]

Hi Ed,

thanks for your fast reply!
I thought it would be possible to do this within the table, but clearly the
answer is no.

I already tried to do the calculation in a form now. This works fine,
although another problem pops up.
When I try to add a graph to this form (with the calculated columns in it) I
can only choose for a query or table as "row source". But then it doesn't add
the calculated columns ofcourse.

So I want to add in the row source something like "FROM [subform1]" but I
can't find the exact way to write it down.

In the "row source" of the graph I got this:
SELECT [Operator],[NS],[NO],[BD],[Vouchers] FROM [table1];

This should be something like this:
SELECT [Operator],[NS],[NO],[BD],[Vouchers] FROM forms![subform1]; or
something like that. Could you, or someone else, help me create the right row
source? This one returns an error.

Thanks in advance!
Kind regards, Onne

 

[Guest]

I decided to first make the query with the calculations and then add a graph.
That works! So no more help needed at this time! Thanks again.

Hi Ed,

thanks for your fast reply!
I thought it would be possible to do this within the table, but clearly the
answer is no.

I already tried to do the calculation in a form now. This works fine,
although another problem pops up.
When I try to add a graph to this form (with the calculated columns in it) I
can only choose for a query or table as "row source". But then it doesn't add
the calculated columns ofcourse.

So I want to add in the row source something like "FROM [subform1]" but I
can't find the exact way to write it down.

In the "row source" of the graph I got this:
SELECT [Operator],[NS],[NO],[BD],[Vouchers] FROM [table1];

This should be something like this:
SELECT [Operator],[NS],[NO],[BD],[Vouchers] FROM forms![subform1]; or
something like that. Could you, or someone else, help me create the right row
source? This one returns an error.

Thanks in advance!
Kind regards, Onne

Column 4 is a calculated column, therefore should not be stored in the
database. So your table should only contain cols 1..3.
to get column 4 you use a query.

Select column1, column2, column3, (column1+column2-column3) as Column4
From Table

If there is any chance at all that any of columns 1,2,3 could be null then
you will want to add the function nz(columnX,0) in the appropriate place to
make that column = 0.

This way when the values in the columns change, the values in the query are
still correct.
Ed Warren.

 

[Ed Warren]

You've got it, base the chart/graph on the query and you're good to go.

Ed Warren.

I decided to first make the query with the calculations and then add a
graph.
That works! So no more help needed at this time! Thanks again.

Hi Ed,

thanks for your fast reply!
I thought it would be possible to do this within the table, but clearly
the
answer is no.

I already tried to do the calculation in a form now. This works fine,
although another problem pops up.
When I try to add a graph to this form (with the calculated columns in
it) I
can only choose for a query or table as "row source". But then it doesn't
add
the calculated columns ofcourse.

So I want to add in the row source something like "FROM [subform1]" but I
can't find the exact way to write it down.

In the "row source" of the graph I got this:
SELECT [Operator],[NS],[NO],[BD],[Vouchers] FROM [table1];

This should be something like this:
SELECT [Operator],[NS],[NO],[BD],[Vouchers] FROM forms![subform1]; or
something like that. Could you, or someone else, help me create the right
row
source? This one returns an error.

Thanks in advance!
Kind regards, Onne

Column 4 is a calculated column, therefore should not be stored in the
database. So your table should only contain cols 1..3.
to get column 4 you use a query.

Select column1, column2, column3, (column1+column2-column3) as Column4
From Table

If there is any chance at all that any of columns 1,2,3 could be null
then
you will want to add the function nz(columnX,0) in the appropriate
place to
make that column = 0.

This way when the values in the columns change, the values in the query
are
still correct.
Ed Warren.

Hi,

I've probably an easy problem but I can't really find a solution on
my
own.

Question:
I have made a table like this:
column1 column2 column3 column4
2 6 4 -
3 12 8 -
4 2 5

What I'm trying to do is a calculation in column4 like this ((column1
+
column2)-column3).
Which should result in resp.
Column4
4
7
1

Can I put a calculation in the column4 properties or do I have to use
a
query?

I thought this couldn't be hard but I really can't find the
solution:S

I would be very gratefull for replies!
Thanks in advance,

greetings Onne