queries & formulas

[Tara]

Hello,

How would I write a formula that uses information from a different line in
the query? For example, I have 4 fields (a,b,c,tot):
a b c tot
10 250 0 =
20 250 50 = + [c]
30 250 100 = + (what c is equal to in the above line)

I've tried to make my formula look something like this:
IIF([a]=10;; IFF([a]=20;+[c];IFF([a]=30;+[c]+([c]=([a]=20));0)))
By using this, I am missing some of my calcuations and, in this example, I
would be short 50.

I have many lines, and the [tot] should be the result of the above line plus
[c]. If there is an easier way to do this than the way I am trying, then
please let me know.

Thanks!
Tara.

 

[Sylvain Lafontaine]

You probably need to make a correlated subquery with a summation:

SELECT a, b, c, b + (Select sum (c) from T1 as T1b where T1b.a <= T1.a) as
tot
from T1
WITH OWNERACCESS OPTION;

 

[Tara]

I'm trying the below, but when I run the query, it asks me for the value
parametres for c. Why would it do that?

You probably need to make a correlated subquery with a summation:

SELECT a, b, c, b + (Select sum (c) from T1 as T1b where T1b.a <= T1.a) as
tot
from T1
WITH OWNERACCESS OPTION;

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hello,

How would I write a formula that uses information from a different line in
the query? For example, I have 4 fields (a,b,c,tot):
a b c tot
10 250 0 =
20 250 50 = + [c]
30 250 100 = + (what c is equal to in the above line)

I've tried to make my formula look something like this:
IIF([a]=10;; IFF([a]=20;+[c];IFF([a]=30;+[c]+([c]=([a]=20));0)))
By using this, I am missing some of my calcuations and, in this example, I
would be short 50.

I have many lines, and the [tot] should be the result of the above line
plus
[c]. If there is an easier way to do this than the way I am trying, then
please let me know.

Thanks!
Tara.

 

[Sylvain Lafontaine]

Just tried on my side and this query works fine. Are you sure that there is
a column C in your table?

Maybe by providing more details - exact definition of your table, exact
query that you have used, version of Access, etc. - I will be able to help
you further; however, at the moment, I cannot tell you anything else excerpt
that I don't understand why this query doesn't work on your system.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I'm trying the below, but when I run the query, it asks me for the value
parametres for c. Why would it do that?

You probably need to make a correlated subquery with a summation:

SELECT a, b, c, b + (Select sum (c) from T1 as T1b where T1b.a <= T1.a)
as
tot
from T1
WITH OWNERACCESS OPTION;

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Hello,

How would I write a formula that uses information from a different line
in
the query? For example, I have 4 fields (a,b,c,tot):
a b c tot
10 250 0 =
20 250 50 = + [c]
30 250 100 = + (what c is equal to in the above line)

I've tried to make my formula look something like this:
IIF([a]=10;;
IFF([a]=20;+[c];IFF([a]=30;+[c]+([c]=([a]=20));0)))
By using this, I am missing some of my calcuations and, in this
example, I
would be short 50.

I have many lines, and the [tot] should be the result of the above line
plus
[c]. If there is an easier way to do this than the way I am trying,
then
please let me know.

Thanks!
Tara.

 

[Tara]

Never mind - I had a typo in one of the definitions of my table.
It works great...thanks for your help!

Just tried on my side and this query works fine. Are you sure that there is
a column C in your table?

Maybe by providing more details - exact definition of your table, exact
query that you have used, version of Access, etc. - I will be able to help
you further; however, at the moment, I cannot tell you anything else excerpt
that I don't understand why this query doesn't work on your system.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I'm trying the below, but when I run the query, it asks me for the value
parametres for c. Why would it do that?

You probably need to make a correlated subquery with a summation:

SELECT a, b, c, b + (Select sum (c) from T1 as T1b where T1b.a <= T1.a)
as
tot
from T1
WITH OWNERACCESS OPTION;

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Hello,

How would I write a formula that uses information from a different line
in
the query? For example, I have 4 fields (a,b,c,tot):
a b c tot
10 250 0 =
20 250 50 = + [c]
30 250 100 = + (what c is equal to in the above line)

I've tried to make my formula look something like this:
IIF([a]=10;;
IFF([a]=20;+[c];IFF([a]=30;+[c]+([c]=([a]=20));0)))
By using this, I am missing some of my calcuations and, in this
example, I
would be short 50.

I have many lines, and the [tot] should be the result of the above line
plus
[c]. If there is an easier way to do this than the way I am trying,
then
please let me know.

Thanks!
Tara.