RunningSum in a Query?

Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct results
and export the data in a table. (I dwant calculated data in a table in order
to use it in some other calculations)

Thanks in advance,
George

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table in
order
to use it in some other calculations)

Thanks in advance,
George

Thanks a lot for your prompt reply,

Sorry but i can't figure it out. I have created the following Query:

SELECT Table2.SerNo, Table2.Amount, Table2.Quantity, [amount]*[quantity] AS
Multiply, DSum("Quantity","table2") AS RunTot
FROM Table2
GROUP BY Table2.SerNo, Table2.Amount, Table2.Quantity, [amount]*[quantity];

How can I get in RunTot the running sum of multipy column? (SrNo is an
Autonumber)

Your help will be highly appreciated,

Thanks

George

Ο χÏ�ήστης "Van T. Dinh" Î*γγÏ�αψε:

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table in
order
to use it in some other calculations)

Thanks in advance,
George

I got it, thanks anyhow:

SELECT Table2.SerNo, Table2.Amount, Table2.Quantity, [amount]*[quantity] AS
Multiply, DSum("[quantity]*[amount]","[table2]","[serno] =" & [serno]) AS
RunningTotal
FROM Table2
GROUP BY Table2.SerNo, Table2.Amount, Table2.Quantity, [amount]*[quantity];


Ο χÏ�ήστης "George" Î*γγÏ�αψε:

Thanks a lot for your prompt reply,

Sorry but i can't figure it out. I have created the following Query:

SELECT Table2.SerNo, Table2.Amount, Table2.Quantity, [amount]*[quantity] AS
Multiply, DSum("Quantity","table2") AS RunTot
FROM Table2
GROUP BY Table2.SerNo, Table2.Amount, Table2.Quantity, [amount]*[quantity];

How can I get in RunTot the running sum of multipy column? (SrNo is an
Autonumber)

Your help will be highly appreciated,

Thanks

George

Ο χÏ�ήστης "Van T. Dinh" Î*γγÏ�αψε:

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table in
order
to use it in some other calculations)

Thanks in advance,
George

I have the same problem with the running sum query.
I have found several examples of how to do this and I have tried creating the
querie in the Northwind sample database and it seems to work fine. However,
when I add my own field names, I get bad calculations or #Error. I have been
working on this for a couple of days because I hate to give up when working
with Excel or Access and I have had some help from the community... But, a
lot of times, through diligence and persistence I am able to get the software
to do what I want it to do. But this time I can't seem to figure it out so I
am hoping someone can help.

Very simply:

I have a query that has a "Date of Month" field which I created in my
table and a Digits field which I use an update query to populate this field
with a 1. Now the date of the month field only shows the numeric month and
of couse there are 4 sometimes 5 weeks in a month so the Date of the Month
field will show the same number 1 - 12 for or 5 times each. This is my group
field.

I have created the columns and included the correct data in the fields but I
can't get the running sum to work properly.


My first field is Date of Month
Second field is Digits _ with all being a 1
Third fields states: Running Total: (DSum("Calendar by Client].[Dates by
Month]","Calendar by Client","[Dates by Month] = " & [Dates by Month]))

I continually get an error or I get the same sum in each field that being 52
because there are 52 records for this particular query. I have broken the
query down all the way to just these three fields and I still can't get it to
work.

Please help!!

Very Discouraged!!






"Van T. Dinh" wrote:

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table in
order
to use it in some other calculations)

Thanks in advance,
George

Is it "Dates by Month" or "Dates of Month"? You say one thing in your
description, but show something different in your expression.

Dates need to be delimited with the "#" character. So your DSum should
probably look more like

Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = #" & [Dates by Month] & "#")

If Dates by Month is a text field then you need to delimit with " marks.
Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = """ & [Dates by Month] & """")

IF Dates by month is a number field then your original expression should
work. Although I wouldn't include the table name as part of the first
argument.
Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = " & [Dates by Month])


"Darius" wrote in message
...
I have the same problem with the running sum query.
I have found several examples of how to do this and I have tried creating
the
querie in the Northwind sample database and it seems to work fine.
However,
when I add my own field names, I get bad calculations or #Error. I have
been
working on this for a couple of days because I hate to give up when
working
with Excel or Access and I have had some help from the community... But,
a
lot of times, through diligence and persistence I am able to get the
software
to do what I want it to do. But this time I can't seem to figure it out
so I
am hoping someone can help.

Very simply:

I have a query that has a "Date of Month" field which I created in my
table and a Digits field which I use an update query to populate this
field
with a 1. Now the date of the month field only shows the numeric month
and
of couse there are 4 sometimes 5 weeks in a month so the Date of the Month
field will show the same number 1 - 12 for or 5 times each. This is my
group
field.

I have created the columns and included the correct data in the fields but
I
can't get the running sum to work properly.


My first field is Date of Month
Second field is Digits _ with all being a 1
Third fields states: Running Total: (DSum("Calendar by Client].[Dates by
Month]","Calendar by Client","[Dates by Month] = " & [Dates by Month]))

I continually get an error or I get the same sum in each field that being
52
because there are 52 records for this particular query. I have broken the
query down all the way to just these three fields and I still can't get it
to
work.

Please help!!

Very Discouraged!!






"Van T. Dinh" wrote:

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it
works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table in
order
to use it in some other calculations)

Thanks in advance,
George

John: I have tried all 3 of these and each time I get #Error. If I could
email you directly I could show you my table and my query with the designs.
I can paste that information here. I really would like to get this resolved.
This is the only way that I will be able to complete the remaining portion
of this database and this small piece is the only thing holding it up. What
I am basically trying to do is to create the Running Total field in a table
so that another query will be able to select the billing date to be the first
week of the month or the second or the third, etc based on this running sum.
I have tried all other methods and this is the one that will work if I can
just get that running sum to work properly. I can make it run properly by
using the dates field and it will show the sequence from 1 through 52 but I
can make it start over for each group (Dates by Month).

Please help.

(PS I took my mind off this project and worked on a couple others that are
resolved and came back to this. Just noticed your email today.

Darius

"John Spencer" wrote:

Is it "Dates by Month" or "Dates of Month"? You say one thing in your
description, but show something different in your expression.

Dates need to be delimited with the "#" character. So your DSum should
probably look more like

Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = #" & [Dates by Month] & "#")

If Dates by Month is a text field then you need to delimit with " marks.
Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = """ & [Dates by Month] & """")

IF Dates by month is a number field then your original expression should
work. Although I wouldn't include the table name as part of the first
argument.
Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = " & [Dates by Month])


"Darius" wrote in message
...
I have the same problem with the running sum query.
I have found several examples of how to do this and I have tried creating
the
querie in the Northwind sample database and it seems to work fine.
However,
when I add my own field names, I get bad calculations or #Error. I have
been
working on this for a couple of days because I hate to give up when
working
with Excel or Access and I have had some help from the community... But,
a
lot of times, through diligence and persistence I am able to get the
software
to do what I want it to do. But this time I can't seem to figure it out
so I
am hoping someone can help.

Very simply:

I have a query that has a "Date of Month" field which I created in my
table and a Digits field which I use an update query to populate this
field
with a 1. Now the date of the month field only shows the numeric month
and
of couse there are 4 sometimes 5 weeks in a month so the Date of the Month
field will show the same number 1 - 12 for or 5 times each. This is my
group
field.

I have created the columns and included the correct data in the fields but
I
can't get the running sum to work properly.


My first field is Date of Month
Second field is Digits _ with all being a 1
Third fields states: Running Total: (DSum("Calendar by Client].[Dates by
Month]","Calendar by Client","[Dates by Month] = " & [Dates by Month]))

I continually get an error or I get the same sum in each field that being
52
because there are 52 records for this particular query. I have broken the
query down all the way to just these three fields and I still can't get it
to
work.

Please help!!

Very Discouraged!!






"Van T. Dinh" wrote:

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it
works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table in
order
to use it in some other calculations)

Thanks in advance,
George

I'm not sure what is failing.

I suggest you post the SQL statement that you are trying to use. -- Select
SQL from the view menu while in design mode --

You might try dropping the square brackets in the string. My field and
table names do not contain spaces or other special characters and I can
never remember if the [] are required in the aggregate functions.

Running Total: DSum("Dates by Month","Calendar by Client","Dates by Month =
#" & [Dates by Month] & "#")


You can also use a subquery to do this.

Field: RunningTotal: (SELECT SUM(C.[Dates by Month]) FROM [Calendar by
Month] as C WHERE C.[Dates by Month] = [Calendar by Month].[Dates by Month]

The [Calendar by Month] as C creates a separate instance of the table (some
people call it an alias) for use
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Rob" wrote in message
news
John: I have tried all 3 of these and each time I get #Error. If I could
email you directly I could show you my table and my query with the
designs.
I can paste that information here. I really would like to get this
resolved.
This is the only way that I will be able to complete the remaining portion
of this database and this small piece is the only thing holding it up.
What
I am basically trying to do is to create the Running Total field in a
table
so that another query will be able to select the billing date to be the
first
week of the month or the second or the third, etc based on this running
sum.
I have tried all other methods and this is the one that will work if I can
just get that running sum to work properly. I can make it run properly by
using the dates field and it will show the sequence from 1 through 52 but
I
can make it start over for each group (Dates by Month).

Please help.

(PS I took my mind off this project and worked on a couple others that are
resolved and came back to this. Just noticed your email today.

Darius

"John Spencer" wrote:

Is it "Dates by Month" or "Dates of Month"? You say one thing in your
description, but show something different in your expression.

Dates need to be delimited with the "#" character. So your DSum should
probably look more like

Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = #" & [Dates by Month] & "#")

If Dates by Month is a text field then you need to delimit with " marks.
Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = """ & [Dates by Month] & """")

IF Dates by month is a number field then your original expression should
work. Although I wouldn't include the table name as part of the first
argument.
Running Total: DSum("[Dates by Month]","Calendar by Client","[Dates by
Month] = " & [Dates by Month])


"Darius" wrote in message
...
I have the same problem with the running sum query.
I have found several examples of how to do this and I have tried
creating
the
querie in the Northwind sample database and it seems to work fine.
However,
when I add my own field names, I get bad calculations or #Error. I
have
been
working on this for a couple of days because I hate to give up when
working
with Excel or Access and I have had some help from the community...
But,
a
lot of times, through diligence and persistence I am able to get the
software
to do what I want it to do. But this time I can't seem to figure it
out
so I
am hoping someone can help.

Very simply:

I have a query that has a "Date of Month" field which I created in my
table and a Digits field which I use an update query to populate this
field
with a 1. Now the date of the month field only shows the numeric month
and
of couse there are 4 sometimes 5 weeks in a month so the Date of the
Month
field will show the same number 1 - 12 for or 5 times each. This is my
group
field.

I have created the columns and included the correct data in the fields
but
I
can't get the running sum to work properly.


My first field is Date of Month
Second field is Digits _ with all being a 1
Third fields states: Running Total: (DSum("Calendar by Client].[Dates
by
Month]","Calendar by Client","[Dates by Month] = " & [Dates by
Month]))

I continually get an error or I get the same sum in each field that
being
52
because there are 52 records for this particular query. I have broken
the
query down all the way to just these three fields and I still can't get
it
to
work.

Please help!!

Very Discouraged!!






"Van T. Dinh" wrote:

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/208714

--
HTH
Van T. Dinh
MVP (Access)



"George" wrote in message
...
Dear gurus, need your help on the following issue:

I have a table in which I have the fields: Amount and Quanity.
I want using a query or programming to achieve the following:

Record 1: Amount 2 Quantity 4 Total=8
Record 2 Amount 2 Quanity 2 Total = 12 (using the running sum as it
works
perfectly in a report)

Is it possible, or can I use the report in which I have the correct
results
and export the data in a table. (I dwant calculated data in a table
in
order
to use it in some other calculations)

Thanks in advance,
George