Query a value from a running sum total

[dp724 via AccessMonster.com]

Hello all,

If you have one field with a running sum total of monthly expenses, that goes
from 0 to 10, the query would produce a list of each individual monthly
expense value. The first monthly expense would "0", the remaining 10
monthly expenses would be "1". Can I create a query which calculates an
expense, if I only have a running sum total?

Can I get some help from you guys?
Thanks in advance.
Dave

 

[Marshall Barton]

If you have one field with a running sum total of monthly expenses, that goes
from 0 to 10, the query would produce a list of each individual monthly
expense value. The first monthly expense would "0", the remaining 10
monthly expenses would be "1". Can I create a query which calculates an
expense, if I only have a running sum total?

I think this kind of thing will do that:

SELECT table.*,
table.RunExpense - (SELECT Sum(X.RunExpense)
FROM table As X
WHERE X.RunExpense < table.RunExpense
) As Expense
FROM table

 

[dp724 via AccessMonster.com]

Thanks Marshall,
Can you help me with setting this up? I tried setting up a field named
"RunExpense" in a table named "Table". I can't set up the query, so the SQL
looks like your suggestion. If I could copy your SQL and paste it, then look
at the set up in Design View then I would better understand. I could really
use some more assistance.

 

[Marshall Barton]

Generally, you should not create or change your
table/fields. Open a new query and immediately switch to
SQL view. Then Copy/Paste what I posted over whatever
Access put in automatically. Read my query for the generic
names I used and replace them with the real names of your
table and fields. You can try the query just by switching
to data sheet view (and back to SQL view). Once you get
past the error messages, if any, then switch to design view
to see how you would enter that stuff in the design grid.
Be aware that Access often rearranges things so they may not
look the same as what you had originally.

 

[dp724 via AccessMonster.com]

I created a database with the one table and one field, using the values I
mentioned in my earlier description, so I could easily implement your query
in the SQL view. Below is your query, as implemented, I don't get the correct
individual monthly expense; and I haven't a clue on how to edited it.

SELECT mytable.runExpense-(SELECT Sum(X.runExpense) FROM mytable As X WHERE X.
runExpense < mytable.runExpense) AS Expense
FROM mytable;

"mytable" is my table
"runExpense" is my field

Honestly, I haven't a clue about the "X", but if don't use it as used in your
query, I get no results. Can you give me some direction.

Thanks.

Generally, you should not create or change your
table/fields. Open a new query and immediately switch to
SQL view. Then Copy/Paste what I posted over whatever
Access put in automatically. Read my query for the generic
names I used and replace them with the real names of your
table and fields. You can try the query just by switching
to data sheet view (and back to SQL view). Once you get
past the error messages, if any, then switch to design view
to see how you would enter that stuff in the design grid.
Be aware that Access often rearranges things so they may not
look the same as what you had originally.

Can you help me with setting this up? I tried setting up a field named
"RunExpense" in a table named "Table". I can't set up the query, so the SQL

[quoted text clipped - 16 lines]

 

[Marshall Barton]

I created a database with the one table and one field, using the values I
mentioned in my earlier description, so I could easily implement your query
in the SQL view. Below is your query, as implemented, I don't get the correct
individual monthly expense; and I haven't a clue on how to edited it.

SELECT mytable.runExpense-(SELECT Sum(X.runExpense) FROM mytable As X
WHERE X.runExpense < mytable.runExpense) AS Expense
FROM mytable;

"mytable" is my table
"runExpense" is my field

Honestly, I haven't a clue about the "X", but if don't use it as used in your
query, I get no results. Can you give me some direction.

I don't see a problem. Please post a small sample of your
data along with the results of running the query on that
data. Also, please explain why the results are not what you
expect them to be.

 

[dp724 via AccessMonster.com]

My data is one table named "mytable" and one field named "runExpense", the
field is filled with a running sum total of values, from 0 to 10, that's 11
records. For "Expense" I get these results:

runExpense Expense
$0.00
$1.00 $1.00
$2.00 $1.00
$3.00 $0.00
$4.00 ($2.00)
$5.00 ($5.00)
$6.00 ($9.00)
$7.00 ($14.00)
$8.00 ($20.00)
$9.00 ($27.00)
$10.00 ($35.00)

I should be getting 0 for the first $0 expense and then get 1 for each of the
remaining monthly expenses; the difference between each running sum total.

I created a database with the one table and one field, using the values I
mentioned in my earlier description, so I could easily implement your query

[quoted text clipped - 10 lines]

Honestly, I haven't a clue about the "X", but if don't use it as used in your
query, I get no results. Can you give me some direction.

I don't see a problem. Please post a small sample of your
data along with the results of running the query on that
data. Also, please explain why the results are not what you
expect them to be.

 

[Marshall Barton]

My data is one table named "mytable" and one field named "runExpense", the
field is filled with a running sum total of values, from 0 to 10, that's 11
records. For "Expense" I get these results:

runExpense Expense
$0.00
$1.00 $1.00
$2.00 $1.00
$3.00 $0.00
$4.00 ($2.00)
$5.00 ($5.00)
$6.00 ($9.00)
$7.00 ($14.00)
$8.00 ($20.00)
$9.00 ($27.00)
$10.00 ($35.00)

I should be getting 0 for the first $0 expense and then get 1 for each of the
remaining monthly expenses; the difference between each running sum total.

I created a database with the one table and one field, using the values I
mentioned in my earlier description, so I could easily implement your query

[quoted text clipped - 10 lines]

Honestly, I haven't a clue about the "X", but if don't use it as used in your
query, I get no results. Can you give me some direction.

I don't see a problem. Please post a small sample of your
data along with the results of running the query on that
data. Also, please explain why the results are not what you
expect them to be.

Argghhh, I typed the wrong function:

Let's try this:

SELECT mytable.*,
mytable.RunExpense - (SELECT Max(X.RunExpense)
FROM mytableAs X
WHERE X.RunExpense < mytable.RunExpense
) As Expense
FROM mytable

Note that without more information in the table (e.g. an
entry date field), two identical entries will produce the
wrong result. If you do have a field that can be used to
determine the previous entry, then the query will be
different:

SELECT mytable.*,
mytable.RunExpense - (SELECT TOP 1 X.RunExpense
FROM mytableAs X
WHERE X.entrydate < mytable.entrydate
ORDER BY X.entrydate DESC
) As Expense
FROM mytable

FYI: Since mytable is used in both the main query and the
subquery, at least one of them must be aliased so you can
distinguish which instance of a field is being referenced.

 

[dp724 via AccessMonster.com]

I'm using your second suggestion and including extra field (No). Just one
problem, perhaps I should have restated it on my last reply. The first run
summing total value should also be listed as the first value in the result
column; I'm getting an empty record, see below. In the case below, the first
expense should be 1.

runExpense No Expense
$1.00 1
$1.00 2 $0.00
$2.00 3 $1.00
$3.00 4 $1.00
$4.00 5 $1.00
$5.00 6 $1.00
$6.00 7 $1.00
$7.00 8 $1.00
$8.00 9 $1.00
$9.00 10 $1.00
$10.00 11 $1.00

I have a feeling you may have anticipated this issue; hope it doesn't
complicate things.

My data is one table named "mytable" and one field named "runExpense", the
field is filled with a running sum total of values, from 0 to 10, that's 11

[quoted text clipped - 26 lines]
Argghhh, I typed the wrong function:

Let's try this:

SELECT mytable.*,
mytable.RunExpense - (SELECT Max(X.RunExpense)
FROM mytableAs X
WHERE X.RunExpense < mytable.RunExpense
) As Expense
FROM mytable

Note that without more information in the table (e.g. an
entry date field), two identical entries will produce the
wrong result. If you do have a field that can be used to
determine the previous entry, then the query will be
different:

SELECT mytable.*,
mytable.RunExpense - (SELECT TOP 1 X.RunExpense
FROM mytableAs X
WHERE X.entrydate < mytable.entrydate
ORDER BY X.entrydate DESC
) As Expense
FROM mytable

FYI: Since mytable is used in both the main query and the
subquery, at least one of them must be aliased so you can
distinguish which instance of a field is being referenced.

 

[Marshall Barton]

I'm using your second suggestion and including extra field (No). Just one
problem, perhaps I should have restated it on my last reply. The first run
summing total value should also be listed as the first value in the result
column; I'm getting an empty record, see below. In the case below, the first
expense should be 1.

runExpense No Expense
$1.00 1
$1.00 2 $0.00
$2.00 3 $1.00
$3.00 4 $1.00
$4.00 5 $1.00
$5.00 6 $1.00
$6.00 7 $1.00
$7.00 8 $1.00
$8.00 9 $1.00
$9.00 10 $1.00
$10.00 11 $1.00

I have a feeling you may have anticipated this issue; hope it doesn't
complicate things.

SELECT mytable.*,
mytable.RunExpense - Nz( (SELECT TOP 1 X.RunExpense
FROM mytableAs X
WHERE X.entrydate < mytable.entrydate
ORDER BY X.entrydate DESC
), 0) As Expense
FROM mytable

 

[dp724 via AccessMonster.com]

Excellent work! Thanks a million.

Considering your very first reply and thinking, I was able to edit your very
first SQL to get it listing a running sum total using your last reply; I'm
sure to use it in the future. Thanks for making this a rewarding experience.

SELECT mytable.*, mytable.runExpense + Nz((SELECT Sum(X.runExpense) FROM
mytable As X WHERE X.runExpense < mytable.runExpense), 0) AS Expense
FROM mytable;

I'm using your second suggestion and including extra field (No). Just one
problem, perhaps I should have restated it on my last reply. The first run

[quoted text clipped - 17 lines]

I have a feeling you may have anticipated this issue; hope it doesn't
complicate things.

SELECT mytable.*,
mytable.RunExpense - (SELECT TOP 1 X.RunExpense [quoted text clipped - 3 lines]
) As Expense
FROM mytable

SELECT mytable.*,
mytable.RunExpense - Nz( (SELECT TOP 1 X.RunExpense
FROM mytableAs X
WHERE X.entrydate < mytable.entrydate
ORDER BY X.entrydate DESC
), 0) As Expense
FROM mytable

 

[dp724 via AccessMonster.com]

Marshall,
The running sum total query I created and posted last is wrong; it doesn't
calculate the first two values correctly. I had found that changing the minus
sign to a plus sign resulted in a running sum, but the first values were
empty. I thought plugging in the "Nz" and ", 0" would fill in the values
which it did; but they're incorrect values. see below.

No runExpense Expense
1 $1.00 $1.00
2 $1.00 $1.00
3 $2.00 $4.00
4 $3.00 $7.00
5 $4.00 $11.00
6 $5.00 $16.00
7 $6.00 $22.00
8 $7.00 $29.00
9 $8.00 $37.00
10 $9.00 $46.00
11 $10.00 $56.00

Can you please help, one last time?
Thanks.

Excellent work! Thanks a million.

Considering your very first reply and thinking, I was able to edit your very
first SQL to get it listing a running sum total using your last reply; I'm
sure to use it in the future. Thanks for making this a rewarding experience.

SELECT mytable.*, mytable.runExpense + Nz((SELECT Sum(X.runExpense) FROM
mytable As X WHERE X.runExpense < mytable.runExpense), 0) AS Expense
FROM mytable;
[quoted text clipped - 15 lines]

), 0) As Expense
FROM mytable

 

[dp724 via AccessMonster.com]

Ok, I'm good. This looks like it.

SELECT mytable.*, mytable.runExpense + Nz((SELECT Sum(X.runExpense) FROM
mytable As X WHERE X.No < mytable.No),0) AS Expense
FROM mytable;

Marshall,
The running sum total query I created and posted last is wrong; it doesn't
calculate the first two values correctly. I had found that changing the minus
sign to a plus sign resulted in a running sum, but the first values were
empty. I thought plugging in the "Nz" and ", 0" would fill in the values
which it did; but they're incorrect values. see below.

No runExpense Expense
1 $1.00 $1.00
2 $1.00 $1.00
3 $2.00 $4.00
4 $3.00 $7.00
5 $4.00 $11.00
6 $5.00 $16.00
7 $6.00 $22.00
8 $7.00 $29.00
9 $8.00 $37.00
10 $9.00 $46.00
11 $10.00 $56.00

Can you please help, one last time?
Thanks.

Excellent work! Thanks a million.

[quoted text clipped - 11 lines]

 

[Marshall Barton]

That's the general idea. The critical piece is that you
must have a unigue sort order so you can figure out which
record is the previous record.

"skills improve with practice" ;-)
--
Marsh
MVP [MS Access]

Ok, I'm good. This looks like it.

SELECT mytable.*, mytable.runExpense + Nz((SELECT Sum(X.runExpense) FROM
mytable As X WHERE X.No < mytable.No),0) AS Expense
FROM mytable;

The running sum total query I created and posted last is wrong; it doesn't
calculate the first two values correctly. I had found that changing the minus
sign to a plus sign resulted in a running sum, but the first values were
empty. I thought plugging in the "Nz" and ", 0" would fill in the values
which it did; but they're incorrect values. see below.

No runExpense Expense
1 $1.00 $1.00
2 $1.00 $1.00
3 $2.00 $4.00
4 $3.00 $7.00
5 $4.00 $11.00
6 $5.00 $16.00
7 $6.00 $22.00
8 $7.00 $29.00
9 $8.00 $37.00
10 $9.00 $46.00
11 $10.00 $56.00

Can you please help, one last time?
Thanks.

Excellent work! Thanks a million.

[quoted text clipped - 11 lines]

), 0) As Expense
FROM mytable