Cumulative column in Query

[Carrie_Loos via AccessMonster.com]

I have been researching and spending time in help - looks like I can write a
sub-query to do this but it is over my head - can anyone assist?

Below is an example of my query and I would like to add the cumlative column
on the end but cannot get it to calculate correctly. It is a very simple
query......

SELECT [Unit Nbr].[Unit Nbr], [Learning Rates].[Learning Rates], [Learning
Rates].b, 1 AS [Unit Time Begin], [Unit Time Begin]*([Unit Nbr]^) AS [Unit
Time 1]
FROM [Unit Nbr], [Learning Rates];


How and where do I enter a 'sub-query'?


Unit Nbr Learning Rates b Unit Unit Time Cumlative
1 70.00% -0.51 1 1.00
2 70.00% -0.51 1 0.70 1.70
3 70.00% -0.51 1 0.57 2.27
4 70.00% -0.51 1 0.49 2.76
5 70.00% -0.51 1 0.44 3.20
6 70.00% -0.51 1 0.40 3.59

Thanks for any help you can offer.
Carrie

 

[Michel Walsh]

Well, if the data is really like what you shown, basically, the cumulative
time, for unit n, would be 1^-0.51 + 2^-0.51 + 3^-0.51 + ... + n^-0.51,
right? Sounds funny, but anyhow, here a way to do it. Save your actual
query, say, under the name q1. Then make another query:

SELECT a.unit,
LAST(a.[Unit Time 1]) As UnitTime,
SUM(b.[Unit Time 1]) AS cumulative
FROM q1 AS a INNER JOIN q1 AS b
ON a.unit >= b.unit
GROUP BY a.unit


should do.



Hoping it may help,
Vanderghast, Access MVP

 

[Carrie_Loos via AccessMonster.com]

What is a.unit and b.unit? Do I need to define them?

Well, if the data is really like what you shown, basically, the cumulative
time, for unit n, would be 1^-0.51 + 2^-0.51 + 3^-0.51 + ... + n^-0.51,
right? Sounds funny, but anyhow, here a way to do it. Save your actual
query, say, under the name q1. Then make another query:

SELECT a.unit,
LAST(a.[Unit Time 1]) As UnitTime,
SUM(b.[Unit Time 1]) AS cumulative
FROM q1 AS a INNER JOIN q1 AS b
ON a.unit >= b.unit
GROUP BY a.unit

should do.

Hoping it may help,
Vanderghast, Access MVP

I have been researching and spending time in help - looks like I can write
a

[quoted text clipped - 23 lines]

Thanks for any help you can offer.
Carrie

 

[Michel Walsh]

a and b are two aliases, pointers, for your table/query, as you can use
TWO of your fingers (the aliases) to run over ONE list of data (the query).


Vanderghast, Access MVP

What is a.unit and b.unit? Do I need to define them?

Well, if the data is really like what you shown, basically, the cumulative
time, for unit n, would be 1^-0.51 + 2^-0.51 + 3^-0.51 + ... + n^-0.51,
right? Sounds funny, but anyhow, here a way to do it. Save your actual
query, say, under the name q1. Then make another query:

SELECT a.unit,
LAST(a.[Unit Time 1]) As UnitTime,
SUM(b.[Unit Time 1]) AS cumulative
FROM q1 AS a INNER JOIN q1 AS b
ON a.unit >= b.unit
GROUP BY a.unit

should do.

Hoping it may help,
Vanderghast, Access MVP

I have been researching and spending time in help - looks like I can
write
a

[quoted text clipped - 23 lines]

Thanks for any help you can offer.
Carrie