a kind of Sum Query

[mezzanine1974]

Say that we have a table with two fields (Date, Money). Suppose that
date increase sequentally and some "Money" value corresponds to each
date in a row. I need to run a query in which i can a have a column to
provide me Sum_of_Money till each date seperately. Output will be
something as below.

Date Money Sum_of_Total
01-01-2008 100$ 100$
02-01-2008 3$ 103$
03-01-2008 20$ 123$

greatly appreceated for any help!

[Jens Schilling]

Hi,

mezzanine1974 wrote:

Say that we have a table with two fields (Date, Money). Suppose that
date increase sequentally and some "Money" value corresponds to each
date in a row. I need to run a query in which i can a have a column to
provide me Sum_of_Money till each date seperately. Output will be
something as below.

Date Money Sum_of_Total
01-01-2008 100$ 100$
02-01-2008 3$ 103$
03-01-2008 20$ 123$

greatly appreceated for any help!

OK, let's see what I have...

You could start here:

How to create a running totals query in Microsoft Access
http://support.microsoft.com/kb/290136/EN-US/

and the samples mentioned there, which you can find (
http://support.microsoft.com/kb/207626/EN-US )

Another sample can be found in Rogers Access Library :

RunningSumInQuery:

http://rogersaccesslibrary.com/downl...SumInQuery.mdb

But maybe this wizard is of interest for you :

Query Add-in for Autonumbers and Running Sums
http://www.fabalou.com/Access/Queries/querycreator.asp

Regards
Jens

[mezzanine1974]

Hi Jens,

Thanks for your response.
I tried SQL below. It is not working! Sum_of_Total column produce
empty column. Do you have any idea?
SELECT Table.Date, Table.Money, DSum("Money","Table","[Table]![Date]
<[Date]") AS Sum_of_Total
FROM [Table];

[Bob Quintal]

mezzanine1974 <sa*************@yahoo.comwrote in
news:271b9b21-c8a7-4f13-93ca-
fd**********@s12g2000prg.googlegroups.co
m:

Hi Jens,

Thanks for your response.
I tried SQL below. It is not working! Sum_of_Total column produce
empty column. Do you have any idea?
SELECT Table.Date, Table.Money,
DSum("Money","Table","[Table]![Date] <[Date]") AS Sum_of_Total
FROM [Table];

Your problem comes from using a reserved word as a field name.
Access will interpret [date] as the field.
Date < date will always return false, so you get no money.

Quick fix:
DSum("Money","Table","[Table]![Date] < #" & date() & "#")

Better fix
Change the [date] field name to [MoneyDate]
DSum("Money","Table","[Table]![MoneyDate] < #" & date() & "#")
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[mezzanine1974]

Hello Bob,
Thanks for your response.
I gave the example above to simplify my question. The SQL i am using
is quite different. Please see below:

SELECT Qry_Total_forChart.[Project ID], Qry_Total_forChart.[PO Date],
DSum("Total","Qry_Total_forChart","[Qry_Total_forChart]![PO Date]
<=[PO Date]") AS Sum_of_Total
FROM Qry_Total_forChart;
***
//SQL above yields Empty Sum_of_Total column
***
SELECT Qry_Total_forChart.[Project ID], Qry_Total_forChart.[PO Date],
DSum("Total","Qry_Total_forChart","[Qry_Total_forChart]![PO Date]
<=#"&[PO Date]&"#") AS Sum_of_Total
FROM Qry_Total_forChart;
***
//SQL above yields Sum_of_Total column but it is not succesfull.
Because some rows repeats the Grand Total which supposed to be get at
the very end row!

SELECT Qry_Total_forChart.[Project ID], Qry_Total_forChart.[PO Date],
DSum("Total","Qry_Total_forChart","#"&[Qry_Total_forChart]![PO
Date]&"# <=#"&[PO Date]&"#") AS Sum_of_Total
FROM Qry_Total_forChart;
***
//SQL above yields Sum_of_Total column but each row repeats the Grand
Total which supposed to be get at the very end row!

I can not get the correct figure. I really searched groups too much
but i can not fix it.

Thanks for your posting anyway.