If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
running balance
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? |
#2
|
|||
|
|||
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" wrote in message ... 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? |
#3
|
|||
|
|||
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" сообщил/сообщила в новостях следующее: ... 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" wrote in message ... 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? |
#4
|
|||
|
|||
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" wrote in message ... 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" сообщил/сообщила в новостях следующее: ... 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" wrote in message ... 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? |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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? . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Running Balance | Shawn | Running & Setting Up Queries | 1 | August 19th, 2004 06:45 PM |
Looped result | cojohnso | Running & Setting Up Queries | 7 | July 24th, 2004 01:25 AM |
Running Balance show at top | SharonSmith | Worksheet Functions | 3 | March 25th, 2004 11:09 PM |
Running balance for cheque book record | Charlie Donohue | Worksheet Functions | 4 | October 1st, 2003 02:37 PM |