Running total in query

D

dje

I am trying to calculate a running total in a query. The fields are date and
net change in balance from which I want to create a bank statement like
running balance for the forseeable future certainly several years. My initial
enquiries indicated the way to go was using datepart and dsum. The resultant
code was:

SELECT [Metal Daily Prices Table].MyDate, [Cash - USD Trans Entries
Query].[Tot Bal USD Equiv], NZ(DatePart("yyyy",[Metal Daily Prices
table].Mydate),0) AS Ayear, Nz(DatePart("m",[Metal Daily Prices
table].Mydate),0) AS Amonth, NZ(DatePart("d",[Metal Daily Prices
table].Mydate),0) AS Aday, CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD
Trans Entries Query]","Datepart('d',[Mydate])<=" & [Aday] & "And
DatePart('m',[Mydate])<=" & [Amonth] & "And Datepart('yyyy',[Mydate])<=" &
[ayear] & ""),0)) AS Runtot
FROM [Metal Daily Prices Table] LEFT JOIN [Cash - USD Trans Entries Query]
ON [Metal Daily Prices Table].MyDate = [Cash - USD Trans Entries
Query].[Value Date]
ORDER BY [Metal Daily Prices Table].MyDate, NZ(DatePart("yyyy",[Metal Daily
Prices table].Mydate),0), Nz(DatePart("m",[Metal Daily Prices
table].Mydate),0), NZ(DatePart("d",[Metal Daily Prices table].Mydate),0);

The code works for one month but when a second month is introduced the
running total reverts to zero and the running total increases in a strange
way i.e. 1/3/07 total is aggregate of 1/2/07 plus 1/3/07, 2/3/07 total is
aggregate of 2/2/07 plus 2/3/07 etc so by 31/3/07 the running total is the
aggregate of the whole of Feb & Mar (but half way through March it does not
include the second half of February amounts)

Am I taking the right approach and if so does anyone know how to get the
correct result? I am a novice so please bear this in mind with any
solutions!!.Thanks
 
G

Gary Walter

dje said:
I am trying to calculate a running total in a query. The fields are date
and
net change in balance from which I want to create a bank statement like
running balance for the forseeable future certainly several years. My
initial
enquiries indicated the way to go was using datepart and dsum. The
resultant
code was:

SELECT [Metal Daily Prices Table].MyDate, [Cash - USD Trans Entries
Query].[Tot Bal USD Equiv], NZ(DatePart("yyyy",[Metal Daily Prices
table].Mydate),0) AS Ayear, Nz(DatePart("m",[Metal Daily Prices
table].Mydate),0) AS Amonth, NZ(DatePart("d",[Metal Daily Prices
table].Mydate),0) AS Aday, CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD
Trans Entries Query]","Datepart('d',[Mydate])<=" & [Aday] & "And
DatePart('m',[Mydate])<=" & [Amonth] & "And Datepart('yyyy',[Mydate])<=" &
[ayear] & ""),0)) AS Runtot
FROM [Metal Daily Prices Table] LEFT JOIN [Cash - USD Trans Entries Query]
ON [Metal Daily Prices Table].MyDate = [Cash - USD Trans Entries
Query].[Value Date]
ORDER BY [Metal Daily Prices Table].MyDate, NZ(DatePart("yyyy",[Metal
Daily
Prices table].Mydate),0), Nz(DatePart("m",[Metal Daily Prices
table].Mydate),0), NZ(DatePart("d",[Metal Daily Prices table].Mydate),0);

The code works for one month but when a second month is introduced the
running total reverts to zero and the running total increases in a strange
way i.e. 1/3/07 total is aggregate of 1/2/07 plus 1/3/07, 2/3/07 total is
aggregate of 2/2/07 plus 2/3/07 etc so by 31/3/07 the running total is the
aggregate of the whole of Feb & Mar (but half way through March it does
not
include the second half of February amounts)

Am I taking the right approach and if so does anyone know how to get the
correct result? I am a novice so please bear this in mind with any
solutions!!.Thanks
Click to expand...

Hi dje,

I gave your table aliases so my simple mind
could follow better...

SELECT
M.MyDate,
C.[Tot Bal USD Equiv],
NZ(DatePart("yyyy",M.Mydate),0) AS Ayear,
Nz(DatePart("m", M.Mydate),0) AS Amonth,
NZ(DatePart("d", M.Mydate),0) AS Aday,
CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD Trans Entries Query]",
"Datepart('d',[Mydate])<=" & [Aday] &
"And DatePart('m',[Mydate])<=" & [Amonth] &
"And Datepart('yyyy',[Mydate])<=" & [ayear] & ""),0)) AS Runtot
FROM
[Metal Daily Prices Table] AS M
LEFT JOIN
[Cash - USD Trans Entries Query] AS C
ON
M.MyDate = C.[Value Date]
ORDER BY
M.MyDate;

the first thing that stands out is what date fields are
in each table, i.e., in join you say

ON
M.MyDate = C.[Value Date]

but, in domain of "C" within DSUM,
you use "MyDate" instead of "[Value Date]"

Do you also have a MyDate in "[Cash - USD Trans Entries Query]"?

If so, then what part does [Value Date] play?

Did you parse out day, month, and year to
not have to deal with US format of full date
in DSUM where clause?

I would have thought (w/o really knowing your data) ...

SELECT
M.MyDate,
C.[Tot Bal USD Equiv],
CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD Trans Entries Query]",
"[Value Date]<= #" & Format(M.MyDate, "mm/dd/yyyy") & "#"),0)) AS Runtot
FROM
[Metal Daily Prices Table] AS M
LEFT JOIN
[Cash - USD Trans Entries Query] AS C
ON
M.MyDate = C.[Value Date]
ORDER BY
M.MyDate;

good luck,

gary
 
D

dje

Gary

Thanks for your quick and easy to understand response. It works perfectly
and I would never have got there on my own.

To answer your specific questions (in case you are interested) My Date and
Value date are the fields where I have joined tables together. There will
always be one mydate entry each day whilst the value date can appear any
number of times per date i.e. zero, one or more times (being bank entries) .
Having created them I have included them in most queries to show the joins.

The Date Parse was my interpretation of a Microsoft article as I thought
this was the only way to recognise a date value.

I think both items show how much of a novice I am - I am doing my best to
understand the Sql coding but have to say I struggle!

Once again - Thanks!

David

Gary Walter said:
dje said:
I am trying to calculate a running total in a query. The fields are date
and
net change in balance from which I want to create a bank statement like
running balance for the forseeable future certainly several years. My
initial
enquiries indicated the way to go was using datepart and dsum. The
resultant
code was:

SELECT [Metal Daily Prices Table].MyDate, [Cash - USD Trans Entries
Query].[Tot Bal USD Equiv], NZ(DatePart("yyyy",[Metal Daily Prices
table].Mydate),0) AS Ayear, Nz(DatePart("m",[Metal Daily Prices
table].Mydate),0) AS Amonth, NZ(DatePart("d",[Metal Daily Prices
table].Mydate),0) AS Aday, CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD
Trans Entries Query]","Datepart('d',[Mydate])<=" & [Aday] & "And
DatePart('m',[Mydate])<=" & [Amonth] & "And Datepart('yyyy',[Mydate])<=" &
[ayear] & ""),0)) AS Runtot
FROM [Metal Daily Prices Table] LEFT JOIN [Cash - USD Trans Entries Query]
ON [Metal Daily Prices Table].MyDate = [Cash - USD Trans Entries
Query].[Value Date]
ORDER BY [Metal Daily Prices Table].MyDate, NZ(DatePart("yyyy",[Metal
Daily
Prices table].Mydate),0), Nz(DatePart("m",[Metal Daily Prices
table].Mydate),0), NZ(DatePart("d",[Metal Daily Prices table].Mydate),0);

The code works for one month but when a second month is introduced the
running total reverts to zero and the running total increases in a strange
way i.e. 1/3/07 total is aggregate of 1/2/07 plus 1/3/07, 2/3/07 total is
aggregate of 2/2/07 plus 2/3/07 etc so by 31/3/07 the running total is the
aggregate of the whole of Feb & Mar (but half way through March it does
not
include the second half of February amounts)

Am I taking the right approach and if so does anyone know how to get the
correct result? I am a novice so please bear this in mind with any
solutions!!.Thanks
Click to expand...

Hi dje,

I gave your table aliases so my simple mind
could follow better...

SELECT
M.MyDate,
C.[Tot Bal USD Equiv],
NZ(DatePart("yyyy",M.Mydate),0) AS Ayear,
Nz(DatePart("m", M.Mydate),0) AS Amonth,
NZ(DatePart("d", M.Mydate),0) AS Aday,
CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD Trans Entries Query]",
"Datepart('d',[Mydate])<=" & [Aday] &
"And DatePart('m',[Mydate])<=" & [Amonth] &
"And Datepart('yyyy',[Mydate])<=" & [ayear] & ""),0)) AS Runtot
FROM
[Metal Daily Prices Table] AS M
LEFT JOIN
[Cash - USD Trans Entries Query] AS C
ON
M.MyDate = C.[Value Date]
ORDER BY
M.MyDate;

the first thing that stands out is what date fields are
in each table, i.e., in join you say

ON
M.MyDate = C.[Value Date]

but, in domain of "C" within DSUM,
you use "MyDate" instead of "[Value Date]"

Do you also have a MyDate in "[Cash - USD Trans Entries Query]"?

If so, then what part does [Value Date] play?

Did you parse out day, month, and year to
not have to deal with US format of full date
in DSUM where clause?

I would have thought (w/o really knowing your data) ...

SELECT
M.MyDate,
C.[Tot Bal USD Equiv],
CDbl(NZ(DSum("[Tot Bal USD Equiv]","[Cash - USD Trans Entries Query]",
"[Value Date]<= #" & Format(M.MyDate, "mm/dd/yyyy") & "#"),0)) AS Runtot
FROM
[Metal Daily Prices Table] AS M
LEFT JOIN
[Cash - USD Trans Entries Query] AS C
ON
M.MyDate = C.[Value Date]
ORDER BY
M.MyDate;

good luck,

gary
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Running Total in Queries 2
query to calc running total 1
DSUM, Running total problem 8
Running Total 10
DSum criteria 1
Dailly Running Totals 6
Running Sum within Query 4
daily interest calculation 3

Top