I copied and pasted your equation, but I ended up with very different
answers
than I was hopeing for! At least it is now only adding one line at a
time.
Think of it as a question of inventory.
a=number of days
total = the inventory I should have after 10, 20, etc. days
I have a couple of different options on how to sell the merchandise
(which
is why each day # is also assigned a cn #) and I would like to compare
the
results every 10 days to see which is the best route to take.
Each case should be treated seperately.
b is only used in the first line to show what my initial total is
after that, I only need to + c - d to the total of the line above (of the
same cn#)
Below, I have seperated my chart by cn# to show you how the calculations
from one chart does not affect the calculations in the other. (however I
still need all this info to show up in one query)
a cn b c d total
10 1 250 0 0 250 (this line is equal to b)
20 1 250 50 25 275 (above line + c - d)
30 1 250 100 30 345
40 1 250 125 50 420
AND
a cn b c d total
10 2 300 0 0 300 (this line is equal to b)
20 2 300 75 40 335 (above line + c - d)
30 2 300 75 40 370
40 2 300 75 40 405
I hope I am clarifying things and not confusing you more!
Tara.
Allen Browne said:
I'm not really clear about the goal, but this example gives the total b
+
c - d of this and previous rows, where "previous" is defined as:
- same A
- lower cn.
SELECT a,
(SELECT Sum(b + c - d) AS Subtotal
FROM Table1 AS Dupe
WHERE Dupe.a = Table1.a
AND Dupe.cn <= Table1.cn) AS Subtotal
FROM Table1
GROUP BY a;
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Allen,
This works except that it doesn't quite give me what I am looking
for. It
has the same problem as the original formula I was using.
The values for a, b, c, d, and cn are all stored in Table1.
I am trying to run a query to find out what total should be.
you can find the same value in [a] more than once
but each value of [a] will be assigned a [cn] number
for example:
a cn
10 1
10 2
20 1
20 2
and so on
in order to calculate [total], I need to do the following:
= (the total amount from the line when a=20 and cn=2) + [c] - [d]
[a] represents a category
[cn] represents a case number
I have two different options on how to sell my merchandise and need
to
compare the end results of both. The [total] needs to add [c] and
subtract
[d] from the previous result of the same [cn] number.
Below, I have included what the results of [total] should be:
(the first line of each [cn] is = )
a cn b c d total
10 1 250 0 0 250
10 2 300 0 0 300
20 1 250 50 25 275
20 2 300 75 40 335
30 1 250 100 30 345
30 2 300 75 40 370
40 1 250 125 50 420
40 2 300 75 40 405
Is this making more sense?
Tara
:
For each value of A, you want the totals of B, C, and D?
SELECT a,
Sum(b) AS SumOfB,
Sum(c) AS SumOfC,
Sum(d) AS SumOfD
FROM Table1
GROUP BY a;
Then you want to add SumOfB and SubOfC, and subtract SumOfD:
SELECT a,
Sum(b) AS SumOfB,
Sum(c) AS SumOfC,
Sum(d) AS SumOfD
SumOfB + SubOfC - SumOfD As Whatever
FROM Table1
GROUP BY a;
I'm not really clear if you are trying to GROUP BY fields other than
A.
If
so, just choose Group By under that field also in the Total row in
query
design.
If columns B, C, and D contain values you want to add or subtract,
there's a
fairly good chance that these columns would be better represented as
multiple rows in a related table, rather than multiple columns in
this
table.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
Hello,
To find the total, I can use the equation below:
a b c d total
10 250 0 0 =
20 250 50 25 = + [c] - [d]
30 250 100 30 =(the total amount from the line above) +
[c] -
[d]
SELECT a, b, c, b + (Select sum (c) from Table1 as T1b where
T1b.a <=
Table1.a) - (Select sum (d) from Table1 as T1d where T1b.a <=
Table1.a)as
tot
from Table1
WITH OWNERACCESS OPTION;
This works great, but what happens if I have different options for
each
a?
For example:
a cn b c d total
10 1 250 0 0 =
10 2 300 0 0 =
20 1 250 50 25 = + [c] - [d]
20 2 300 75 40 = + [c] - [d]
30 1 250 100 30 =(the total amount from the line
when
a=20
and cn=1) + [c] - [d]
30 2 300 75 40 =(the total amount from the
line
when
a=20 and cn=2) + [c] - [d]
40 1 250 125 50 =(the total amount from the line
when
a=30
and cn=1) + [c] - [d]
40 2 300 75 40 =(the total amount from the
line
when
a=30 and cn=2) + [c] - [d]
Is there a way to do this, or am I getting too complicated?
Thank you for any help.
Tara
