running balance

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I am trying to use a running balance in a query, similar
to how your checking account works. I can't figure out
how to do this. Can anyone help?
 
The trick to this is to create a subquery on the same table that contains
all records less than the parent date.

Here is the "detail" sub query B1qryDetail
SELECT E.Julian, sub.Julian, sub.Rate
FROM Exchange AS E, Exchange AS sub
WHERE (((sub.Julian)<=[e].[julian]));

Then Group the B1qry on E.Julian, Sum on Rate
ELECT B.E.Julian, B.Rate
FROM B1qryDetail AS B;


This is a one query view.
SELECT B.BalDate, Sum(B.Rate) AS SumOfRate
FROM
(SELECT E.Julian AS BalDate, sub.Julian, sub.Rate
FROM Exchange AS E, Exchange AS sub
WHERE (((sub.Julian)<=[e].[julian])))
AS B
GROUP BY B.BalDate;

Stephen Rasey
Houston
http://excelsig.org
 
Stephen
I had the same problem - couldn't make a query with the running sum. I used
a report instead - with the 'running sum' option enabled.
I tried your solution - first I was getting the wrong results - the totals
were much more than they should be - until I used 'distinct' in after
'Select' statement - it seems to work now.

Thanks a lot!


Alexandr Artmonov


Stephen Rasey said:
The trick to this is to create a subquery on the same table that contains
all records less than the parent date.

Here is the "detail" sub query B1qryDetail
SELECT E.Julian, sub.Julian, sub.Rate
FROM Exchange AS E, Exchange AS sub
WHERE (((sub.Julian)<=[e].[julian]));

Then Group the B1qry on E.Julian, Sum on Rate
ELECT B.E.Julian, B.Rate
FROM B1qryDetail AS B;


This is a one query view.
SELECT B.BalDate, Sum(B.Rate) AS SumOfRate
FROM
(SELECT E.Julian AS BalDate, sub.Julian, sub.Rate
FROM Exchange AS E, Exchange AS sub
WHERE (((sub.Julian)<=[e].[julian])))
AS B
GROUP BY B.BalDate;

Stephen Rasey
Houston
http://excelsig.org


Rob said:
I am trying to use a running balance in a query, similar
to how your checking account works. I can't figure out
how to do this. Can anyone help?
Click to expand...
Click to expand...
 
Yes, Neglecting to use DISTINCT is an oversight. If your table is
normalized and you group by the primary key, you should not need it. But
it is easy to forget and difficult to detect. I wonder what kind of
perfomance penalty you incur if you do not need it?

That SQL was designed without order base calculations never ceases to amaze
me. It can only be a triumph of the set theory purists. SQL also has
weak modularity compared to other languages. Finally, I cannot get over
the fact that you cannot " make table in memory " for intermediate results.

There are some implementations of SQL with some of these more practical
extensions.

I once heard a story in the early 80's that once an IBM VP admitted at a
computer convention that "JCL was the worst mistake ever foisted upon the
computer industry." Perhaps it is apocryphal, but JCL was a pretty obtuse
language.

SQL may not be as thoroughly bad, but it is so widespread that SQL must rank
high in
SELECT Sum(L.SINS*L.Implementations) As Mistake
FROM Languages as L
GROUP BY L.Language
ORDER BY Mistake DESC;

Buy what do I know. I'm a spreadsheet number cruncher.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org




Alexandr Artamonov said:
Stephen
I had the same problem - couldn't make a query with the running sum. I used
a report instead - with the 'running sum' option enabled.
I tried your solution - first I was getting the wrong results - the totals
were much more than they should be - until I used 'distinct' in after
'Select' statement - it seems to work now.

Thanks a lot!


Alexandr Artmonov


Stephen Rasey said:
The trick to this is to create a subquery on the same table that contains
all records less than the parent date.

Here is the "detail" sub query B1qryDetail
SELECT E.Julian, sub.Julian, sub.Rate
FROM Exchange AS E, Exchange AS sub
WHERE (((sub.Julian)<=[e].[julian]));

Then Group the B1qry on E.Julian, Sum on Rate
ELECT B.E.Julian, B.Rate
FROM B1qryDetail AS B;


This is a one query view.
SELECT B.BalDate, Sum(B.Rate) AS SumOfRate
FROM
(SELECT E.Julian AS BalDate, sub.Julian, sub.Rate
FROM Exchange AS E, Exchange AS sub
WHERE (((sub.Julian)<=[e].[julian])))
AS B
GROUP BY B.BalDate;

Stephen Rasey
Houston
http://excelsig.org


Rob said:
I am trying to use a running balance in a query, similar
to how your checking account works. I can't figure out
how to do this. Can anyone help?
Click to expand...
Click to expand...
Click to expand...
 
Hi Rob

Here's a function you can use to calculate a running balance on a form:

Function RunSum (F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'***********************************************************

' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'***********************************************************
Dim RS As Recordset
Dim Result

On Error GoTo Err_RunSum

' Get the form Recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE

RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunSum
End Select

' Compute the running sum.
Do Until RS.BOF
Result = Result + RS(FieldToSum)

' Move to the previous record.
RS.MovePrevious
Loop

Bye_RunSum:
RunSum = Result
Exit Function

Err_RunSum:
Resume Bye_RunSum

End Function


Hope this helps

Maurice St-Cyr
Micro Systems Consultants, Inc
 
Thanks for your help Maurice!

-----Original Message-----
Hi Rob

Here's a function you can use to calculate a running balance on a form:

Function RunSum (F As Form, KeyName As String, KeyValue, _
FieldToSum As String)
'************************************************** *********

' FUNCTION: RunSum()
' PURPOSE: Compute a running sum on a form.
' PARAMETERS:
' F - The form containing the previous value to
' retrieve.
' KeyName - The name of the form's unique key field.
' KeyValue - The current record's key value.
' FieldToSum - The name of the field in the previous
' record containing the value to retrieve.
' RETURNS: A running sum of the field FieldToSum.

' EXAMPLE: =RunSum(Form,"ID",[ID],"Amount")
'************************************************** *********
Dim RS As Recordset
Dim Result

On Error GoTo Err_RunSum

' Get the form Recordset.
Set RS = F.RecordsetClone

' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value?
Case DB_INTEGER, DB_LONG, DB_CURRENCY, _
DB_SINGLE, DB_DOUBLE, DB_BYTE

RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value?
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value?
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
MsgBox "ERROR: Invalid key field data type!"
GoTo Bye_RunSum
End Select

' Compute the running sum.
Do Until RS.BOF
Result = Result + RS(FieldToSum)

' Move to the previous record.
RS.MovePrevious
Loop

Bye_RunSum:
RunSum = Result
Exit Function

Err_RunSum:
Resume Bye_RunSum

End Function


Hope this helps

Maurice St-Cyr
Micro Systems Consultants, Inc

I am trying to use a running balance in a query, similar
to how your checking account works. I can't figure out
how to do this. Can anyone help?
Click to expand...


.
Click to expand...
 
Back
Top