I assume that the field 'a' supplies the ordering of the records.
SELECT u.a, u.b, u.c, Nz(SUM(v.c), 0) AS sumOfPreviousRecords
FROM yourTable AS u LEFT JOIN yourTable AS v
ON u.a > v.a
GROUP BY u.a, u.b, u.c
Will return, for each record,it values (u.a, u.b, u.c) and, also, the sum
of values under column c, for all the previous records. In other words, the
alias u can be seen as the actual record, and alias v to represent ALL
previous records before it. So, SUM(v.c) is the sum of their c values. If
you need JUST the latest record, assuming values under field a are
continuous, without interruption:
SELECT u.a, u.b, u.c, Nz(SUM(v.c), 0) AS sumOfPreviousRecords,
Nz(LAST(w.c) , 0) AS cOfThePreviousRecord
FROM (yourTable AS u LEFT JOIN yourTable AS v
ON u.a > v.a) LEFT JOIN yourTable AS w
ON u.a = w.a+1
GROUP BY u.a, u.b, u.c
Now, from your definition, I must say I am stuck, because you mix unknown
quantities with what has to be computed. As example:
Total% = TotalY / TotalX
Nice, I know TotalY but what is TotalX? you define it AFTER. Not good. Can
you define everything FROM what we already know, to get what we don't. In
other words, can you define the quantities we have to compute by involving
ONLY:
the actual values: u.a, u.b, u.c
the values of JUST the previous record: LAST(w.a), LAST(w.b), LAST(w.c)
all the previous records, aggregated: SUM(v.c) as example, supplying the
sum of the 'c' from all the previous record; while LAST(w.c) return the 'c'
value of only the previous record.
It is not always possible to do so, but by experience, it is generally
possible. I mean, you should be able to define the quantities to be computed
from the values u.c, LAST(w.c) and SUM(v.c) WITHOUT having to reference to
any previous computation result made for a previous records.
If it is impossible to define the quantities to compute without referring to
previously computed results, for previous row, Jet-SQL has no specific
construction to do it, and you will have to use a recordset approach,
looping through the records, one at a time, caching the result of the
computation, into VBA variables, to be re-used in the next record seen by
the loop.
Vanderghast, Access MVP
Tara said:
Hi Michel,
Thank you for your suggestions. I have been trying to build this up in
two
queries as you said, but either end up with the wrong answers or a
circular
reference.
I am going to show you what I am trying to do in a chart form as I may
have
written part of the original SQL I gave you incorrectly (I am still new to
writing them out, and as I can't check this one, there can easily be an
error
in it).
a b c c? TotalX Total% TotalY
1 100 100 8% 8
2 100 2 25 125 10% 12
3 100 12 125 250 8% 20
4 100 20 250 500 5% 25
5 100 2.5 50 550 6% 32
a, b, c come from Table1
TotalY comes from Query1
Total% = TotalY / TotalX
TotalX = b + sum(c?) (from the previous lines only)
c? = c / Total% of previous line
(ie: if a=3, then c?=12/10%)
Does this help show what I am trying to do a little bit more clearly? Do
you
see why I can't build this in two queries.everything is somehow dependent
on
another calculation.
c? depends on the total% of the previous line which depends on TotalX
which
depends on c?
How would you write the SQL for this?
(the only thing I don't need to see in my query is c, but I have included
it
in the chart above to help show where c? comes from)
Tara.
Michel Walsh said:
Another way to see it is maybe to re-write
([Table1].[c]/([tb].[TotalY]/([Table1].
+(SELECT Sum( [c?] ) FROM
[SomeQuery] AS Dupe WHERE [Dupe].[a]<[ThisQuery].[a]))) AS
[Modified_Sum_of_c?]
and now, in [SomeQuery], probably a query you have to create, there,
define
what is [c?].
Again, what you have, right now, is something like:
(SELECT SUM(revenues) FROM here) AS revenues
while what you want is probably something like:
(SELECT SUM(small_revenues) FROM somewhereElse) AS total_revenues
Vanderghast, Access MVP
Michel Walsh said:
I don't know, but since it seems that you are writing the query
[ThisQuery], what you typed is 'recursive' and that cannot be done with
Jet, neither it sounds right. As example, what are you trying to do
with:
([Table1].[c]/([tb].[TotalY]/([Table1].+(SELECT Sum([c?]) FROM
[ThisQuery] AS Dupe WHERE [Dupe].[a]<[ThisQuery].[a]))) AS [c?]
which can be, for simplicity, written as something like:
a + (SELECT SUM(b) FROM thisQuery) AS b
You see, if I ask you what is 'b', in this query, you reply: the sum
of
values b, in this query. That may make sense, in some carefully
designed
context, but here, that leaves b as undefined.
You probably need 2 queries, one that defines what is 'b', without
SUM-ing
it.
Then, in a second query, bring the query you just did, and now, in this
second query, SUM(b) (and b being defined in the first query, without
reference to the second query, that makes the computation simple to be
'cascaded' ).
Hoping it may help,
Vanderghast, Access MVP
I'm sorry....where should I use [ThisQuery]?
In the example I showed you below, I wrote that in lieu of the name of
the
query itself, so as to show that the calculation is coming from the
same
query (but from the previous line).
By replacing [ThisQuery] with the actual name of the query will only
result
in a circular reference which is what I am trying to figure out how to
avoid.
:
You use the name:
[ThisQuery]
it is not the name of the query you are writing, right?
Vanderghast, Access MVP
Hello,
Can someone please help me with the calculation for [c?]
I've tried to write out an SQL below, but it doesn't work because
of a
circular reference. (other than the circular reference, I believe
that
it
is
correct but I am not able to double check as I keep getting the
circular
reference)
[c?] is dependent on the % of [TotalX] and [TotalY] from the
previous
year
(a)
[TotalX] is dependent on how much [c?] is used during that year
If I reinsert the calculation for [c?] in [TotalX], then [c?] will
still
appear in the new calculation! I have also tried to build this up
using 2
queries, but that isn't working either.
SELECT [Table1].[a], [Table1].,
([Table1].[c]/([tb].[TotalY]/([Table1].+(SELECT Sum([c?]) FROM
[ThisQuery] AS Dupe WHERE [Dupe].[a]<[ThisQuery].[a]))) AS [c?],
[Table1].+(SELECT Sum([c?]) FROM [ThisQuery] AS Dupe WHERE
[Dupe].[a]<=[ThisQuery].[a]) AS [TotalX],
[Query1].[TotalY]/([Table1].+(SELECT Sum([c?]) FROM [ThisQuery]
AS Dupe WHERE [Dupe].[a] <=[ThisQuery].[a]) AS [Total%],
[Query1].[TotalY]
FROM ([Table1] INNER JOIN [Query1] ON [Table1].[a] = [Query1].[a])
LEFT
JOIN
[Query1] AS tb ON [Query1].[a]=tb.([a]+1)
WITH OWNERACCESS OPTION;
If there is anything I can do to get around this problem, then
please
let
me
know.
Thank you very much for any help and/or suggestions.
Tara.
