Pls Help -- Tek-Tip "Running Sum In Queries"

[Tom]

I need to create a function that will allow me to use a "Running Sum" in a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:

Let's say I have the following values:

TableName: "SomeTable"
FieldName: "BudgetAmount"
(plus other field names e.g. "Priority")

Basically, the query contains 2 fields:
- Priority
- BudgetAmount


Sample Data in Query -- 1st Execution
=====================================

Priority BudgetAmount ExpressionShowingResultsBasedofModule
-------- ------------ -------------------------------------
1 75.00 75.00
2 100.00 175.00
3 50.00 225.00


Sample Data in Query -- 2nd Execution (Priority has changed!)
=====================================

Priority BudgetAmount ExpressionShowingResultsBasedofModule
-------- ------------ -------------------------------------
1 50.00 50.00
2 100.00 150.00
3 75.00 225.00


Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

2. Do I call my query "qryTest"?

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?


Again, I would really appreciate any help with this? Pls bear in mind that
I haven't used modules before.


Thousand thanks!!!

Tom

 

[Douglas J. Steele]

In-line below.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I need to create a function that will allow me to use a "Running Sum" in a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based on your
example, Priority would seem to fulfill this requirement. idValue represents
what value you're trying to add up to. sumField represents the name of the
field you want to sum (BudgetAmount in your case). As you'll see below, you
need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority

 

[Tom]

Doug:

Thanks for the info below... I got this to work now w/ my sample file.

Unfortunately, it won't work my my "real" file.

Here's where the difference (sample vs. real file) lies.

Sample File:
- Uses "Priority" (1,2,3) field which has unique data values

Real File:
- The value in the "Priority" field is not selected. It is calculated
based on other factors. For instance, the value in "Priority" is calculated
as follows (this line is sample data again).

Priority: ([X]*0.6+[Y]*0.1+[Z]*0.4)

Additional Questions:
1. Since "Priority" is not a field that stores data, can I still use it and
just make reference in the RunSum expression to automatically calculated
"Priority"?
2. There are cases the Priority contains identical values. I tested it with
the "sample file" (data = 1, 2, 3) where I took the PK off the Priority
field. I then used twice "1" and once "3". At that time, I didn't get a
subtotal for the 2nd "1st" Priority?
2. Since the Priority field is currently the "driver" as to how the records
are being sorted, do you know of a workaround solution where I could another
field for the primary key, but that field won't impact the ASC order of the
records, so essentially I still can sort by "Priority"?



Thanks again,
Tom

In-line below.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I need to create a function that will allow me to use a "Running Sum" in a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based on your
example, Priority would seem to fulfill this requirement. idValue represents
what value you're trying to add up to. sumField represents the name of the
field you want to sum (BudgetAmount in your case). As you'll see below, you
need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority

 

[Tom]

Doug:

I just tested the Priority field when it's being calculated (rather than
being a data field).

I get the error "#Error" in the query.

Is there a way to maybe change the expression format to accomodate a
calculated field? Or would it require to rewrite some code in the module?
If that's the case, would you be so kind and please provide me some pointers
as to what line of code might require a change?

Thanks in advance,
Tom

In-line below.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I need to create a function that will allow me to use a "Running Sum" in a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based on your
example, Priority would seem to fulfill this requirement. idValue represents
what value you're trying to add up to. sumField represents the name of the
field you want to sum (BudgetAmount in your case). As you'll see below, you
need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority

 

[Douglas J. Steele]

If Priority doesn't exist as a field in your table, this approach isn't
going to work.

You must have some way of knowing which record in the table each row
corresponds, so that the function can add all of the values from the
beginning to the current one.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug:

I just tested the Priority field when it's being calculated (rather than
being a data field).

I get the error "#Error" in the query.

Is there a way to maybe change the expression format to accomodate a
calculated field? Or would it require to rewrite some code in the module?
If that's the case, would you be so kind and please provide me some pointers
as to what line of code might require a change?

Thanks in advance,
Tom

In-line below.

in

a

query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based on your
example, Priority would seem to fulfill this requirement. idValue represents
what value you're trying to add up to. sumField represents the name of the
field you want to sum (BudgetAmount in your case). As you'll see below, you
need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority

 

[Tom]

Doug:

Since I'll make the changes in a changes to variables in a form,
would it be possible to do the following?

- Make change in form
- Change calls query to requery
- Drop table
- Make table
- Pull data from newly created table

Tom

If Priority doesn't exist as a field in your table, this approach isn't
going to work.

You must have some way of knowing which record in the table each row
corresponds, so that the function can add all of the values from the
beginning to the current one.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug:

I just tested the Priority field when it's being calculated (rather than
being a data field).

I get the error "#Error" in the query.

Is there a way to maybe change the expression format to accomodate a
calculated field? Or would it require to rewrite some code in the module?
If that's the case, would you be so kind and please provide me some pointers
as to what line of code might require a change?

Thanks in advance,
Tom

Sum"

in

a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based

on
your

example, Priority would seem to fulfill this requirement. idValue represents
what value you're trying to add up to. sumField represents the name of the
field you want to sum (BudgetAmount in your case). As you'll see

below,
you

need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority

 

[Tom]

Doug:

I'm afraid my own suggested way doesn't work. I can't drop the table
because the table is linked to the query which is linked to the form.

While the form is open (and essentially it's underlying table), I get the
error message that another process or person uses the table.

Would you happen to know of a work-around where I calculate the "Priority"
value, then e.g. "pass" value into a temp table, and then whatever...?

Again, the user changes data variables in a form. When those variables are
changed, the "Priority" will change due to recalculation, which then
requires resorting of the "Priority" which should kick off the
re-calculation the Running Sum. Makes sense?

Tom

If Priority doesn't exist as a field in your table, this approach isn't
going to work.

You must have some way of knowing which record in the table each row
corresponds, so that the function can add all of the values from the
beginning to the current one.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug:

I just tested the Priority field when it's being calculated (rather than
being a data field).

I get the error "#Error" in the query.

Is there a way to maybe change the expression format to accomodate a
calculated field? Or would it require to rewrite some code in the module?
If that's the case, would you be so kind and please provide me some pointers
as to what line of code might require a change?

Thanks in advance,
Tom

Sum"

in

a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this is the
global running sum routine):"... is this something I should be aware of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based

on
your

example, Priority would seem to fulfill this requirement. idValue represents
what value you're trying to add up to. sumField represents the name of the
field you want to sum (BudgetAmount in your case). As you'll see

below,
you

need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule" with the
statement above?
b. Do I need to make adjustments to the statement above? E.g. replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority

 

[Douglas J. Steele]

Sorry: I don't know your exact situation.

You should be able to add to a temporary table using an INSERT INTO query,
or else use VBA code to add records to the table.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug:

I'm afraid my own suggested way doesn't work. I can't drop the table
because the table is linked to the query which is linked to the form.

While the form is open (and essentially it's underlying table), I get the
error message that another process or person uses the table.

Would you happen to know of a work-around where I calculate the "Priority"
value, then e.g. "pass" value into a temp table, and then whatever...?

Again, the user changes data variables in a form. When those variables are
changed, the "Priority" will change due to recalculation, which then
requires resorting of the "Priority" which should kick off the
re-calculation the Running Sum. Makes sense?

Tom

If Priority doesn't exist as a field in your table, this approach isn't
going to work.

You must have some way of knowing which record in the table each row
corresponds, so that the function can add all of the values from the
beginning to the current one.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Doug:

I just tested the Priority field when it's being calculated (rather than
being a data field).

I get the error "#Error" in the query.

Is there a way to maybe change the expression format to accomodate a
calculated field? Or would it require to rewrite some code in the module?
If that's the case, would you be so kind and please provide me some pointers
as to what line of code might require a change?

Thanks in advance,
Tom



In-line below.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


I need to create a function that will allow me to use a "Running

Sum"

in
a
query without being dependent on a unique ID.

I came across the article below, but I don't get it to work properly.

http://www.tek-tips.com/faqs.cfm?fid=5268

Could someone please provide me additional pointers to get this to work:
Here are my questions (based on the info presented in the URL:
http://www.tek-tips.com/faqs.cfm?fid=5268

1. "Next . . . . add the following code to the same module (this

is
the

global running sum routine):"... is this something I should be aware
of...
of do I just open a new module and simply paste the code in it (with
appropriate adjustments)?

Yup: just open a new module and paste the code into it.

2. Do I call my query "qryTest"?

Either that, or change the reference to qryTest in the routine to whatever
you named your query.

The query would be something like

SELECT Priority, BudgetAmount FROM SomeTable ORDER BY Priority

3. '**********************************************
'* idName - Unique field name. *
'* idValue - Value of unique field. *
'* sumField - The name of the field to runsum *
'**********************************************
a. Do I need a unique field e.g. "idName"?
b. What is the "idValue"? How does it relate to "idName"?
c. Must I change "sumField" to "BudgetAmount"?

The three variables above are what they're called in the function. You
don't
need to change anything there.

Yes, your table should have a single field as its Primary Key. Based on
your
example, Priority would seem to fulfill this requirement. idValue
represents
what value you're trying to add up to. sumField represents the name

of
the

field you want to sum (BudgetAmount in your case). As you'll see below,
you
need to tell the function these things when you call it, as opposed to
changing the function.

4. "Double ... RunSum: CDbl(qryRunSum("SumID",[SumID],"curSum"))"
a. Do I just replace "ExpressionShowingResultsBasedofModule"

with
the

statement above?
b. Do I need to make adjustments to the statement above? E.g.
replace
"SumID" with something else? If yes, what's
the value based on my sample data listed previously?

You're going to create a second query that's going to use the function.

The second query would be something like:

SELECT Priority, BudgetAmount,
CDbl(qryRunSum("Priority",[Priority],"BudgetAmount")) AS RunSum
FROM SomeTable ORDER BY Priority