Problem with running balance

[David57]

Hi,
The following code seems to work fine. The only problem is that the balance for each recorded transaction is related to the immediately previous one (previous row in the subform) and not the one being recorded so that all balances are offset by just one recording. Any Idea for getting the correct running balance?

Private Sub DomesticInv_AfterUpdate()
Dim Sum1 As Single
Dim Sum2 As Single
Dim CstId As Single
Dim stCriteria
CstId = Forms!F_Ledger!IDTblCustomers
Sum1 = Nz(DSum("DomesticInv", "TblLedger", "CstName=" & CstId), 0)
Sum2 = Nz(DSum("ForeignInv", "TblLedger", "CstName=" & CstId), 0)
Forms![F_Ledger]![SF_Ledger].Form![Balance].Value = Sum1 + Sum2
End Sub

[zmbd]

Just to be sure, the result shown for Record 5 is what should be shown for Record 4, Record 4 has Record 3's result, etc...

Whata I would check is if
CstId = Forms!F_Ledger!IDTblCustomers
is returning the correct value.
- Insert a STOP command between lines 6 and 7
- Once the code breaks <ctrl><g>
In the immediate pane: ?CstId[enter]
(or you can open the locals Menu>View>Locals Window)
In either case you can get the value of the CstID
[F8] to step thru the code so that you can check that the DSUM() is actually pulling the correct records, not sure how your data is setup; however, an aggregate query against the [CstNam] might work to allow you to verify the calculated results in your code:

SELECT TblLedger.CstNam
   , Sum(TblLedger.DomesticInv) AS Sum_DomInv
   , Sum(TblLedger.ForeignInv) AS Sum_FrnInv
   , [Sum_DomInv]+[Sum_FrnInv] As Total_Inv
FROM TblLedger
GROUP BY TblLedger.CstNam;

I'm fairly certain that this query will duplicate what you are doing in code.

You might take a look at:
ACC2000: Sample Function to Create a Running Sum on a Form

Forms are difficult to get this to work as the record order is somewhat fluid. What you might think of as logically, and visually, as previous and next records are not guaranteed to be that way.

[David57]

Please do not consider statement no. 5. Just a mistake of transcription.
CstId is getting the right value.
The balance shown, say, in record 4 is really the result that should have been shown for record 3 etc.

David

[zmbd]

Did you try the query I posted?
How does its results compare with the expected values?

[David57]

I tried the query you posted and found that it returns, for each customer, the correct total balance.
What I am striving to obtain, however, is not a global balance but a “step by step” balance after each transaction recorded for any customer. These balances are in field “balance”. The code I posted is doing this but, as I explained, all balances shown for one customer (otherwise correct) seem to be offset just by one transaction. My problem is bringing balances into line, if you know what I mean.
David

[PhilOfWalton]

Running sums are always difficult.

Obviously you are wanting the results by customer, but there is a missing bit of information and that is the sort sequence. Is there a UNIQUE transaction date for each customer or is there a guaranteed TransactionID that will be in the correct order. If there could be more than 1 transaction for a single customer on a single date, will the Transaction Date TransactionID combination be in the correct order?

Suppose the date that I mentioned is OK - 1 transaction per day per customer.
Basically what you do is is do a DSum of all transactions for that customer up to and including that date. That gives a running sum.

I would need to see the table structure and preferably some data to advise further.

Phil

[zmbd]

David,
+ As Phil said, there's a need for the unique record ID or a unique date/time for the transaction. The DSUM() function you are using isn't going to do this for you "straight out of the box" with a simple criteria.

Instead you're going to have do something along these lines:
ACC2003: running totals query
Perhaps including the Customer's id in the criteria. Personally, I do these running sums in a query.

+ Also I mentioned in Post#2 that the sort order doesn't always follow what one is thinking.

+ Have you followed the link I in Post#2 and took a look at method used to create the running sum on a form. It's been one of the most reliable methods I've found over the years, even in subforms. There are other methods; however, they will occasionally miscalculate the running sum.

[David57]

The following is the table structure of TblLedger:

IDTblLedger (autonumber)
Date (date)
Description (text)
CstName (Lookup)
DomesticInv (number)
ForeignInv (number)
Balance (number)

An example of some data entered (the way it is working now):

1/1/16 foreign inv no. 25/Mr. White/0/150/0
1/2/16 domes inv no. 30/Mr. Black/50/0/150
1/3/16 foreign inv no. 35/Mr. Grey/0/100/200
1/4/16 domes inv no. 40/Mr. Trek/70/0/300

And how they should be:

1/1/16 foreign inv no. 25/Mr. White/0/150/150
1/2/16 domes inv no. 30/Mr. Black/50/0/200
1/3/16 foreign inv no. 35/Mr. Grey/0/100/300
1/4/16 domes inv no. 40/Mr. Trek/70/0/370

I have looked at the links suggested but my knowledge of Access is rather basic and they seem terribly difficult for me to understand.

[PhilOfWalton]

OK Several things before we start.
In your table, "Date" is a reserved word, so may I suggest you change this to LedgerDate
CstName as a lookup is a bad idea. I would suggest you have a table if customers with the Key IDTblCustomers (I think you already gave this, and the TblLedger then has IDTblCustomers instead of CstName.
You need to establish a relationship between the 2 tables and enforce referential integrity.

Much cleaner approach!

So My Ledger table looks like this

    IDTblLedger    Autonumber    key
    LedgerDate     Date
    DomesticInv    Number
    ForeignInv     Number
    ....
 

OK 2 options on a query to give the required results, as you failed to answer my questions.

The first is if the entries are in date order (British Dates)

SELECT TblLedger.*, 
Nz(DSum("DomesticInv","TblLedger","LedgerDate <= #" & Format([LedgerDate],"Medium Date") 
& "# AND IDTblCustomers =" & [IDTblCustomers]))
+Nz(DSum("ForeignInv","TblLedger","LedgerDate <= #" & Format([LedgerDate],"Medium Date") 
& "# AND IDTblCustomers =" & [IDTblCustomers])) AS Balance
FROM TblLedger
ORDER BY TblLedger.LedgerDate;
 

and the second if the TblLedgerID are in the correct order

SELECT TblLedger.*, 
Nz(DSum("DomesticInv","TblLedger","IDTblLedger <= " & [IDTblLedger] 
& " AND IDTblCustomers = " & [IDTblCustomers]))
+Nz(DSum("ForeignInv","TblLedger","IDTblLedger <= " 
& [IDTblLedger] & " AND IDTblCustomers = " & [IDTblCustomers])) AS Balance
FROM TblLedger
ORDER BY TblLedger.IDTblLedger;
 

Phil

[David57]

Thank you, Phil, for your prompt reply.
Sorry for the examples previously posted.They were meant, really for a customer based form. So they properly should written like this:

Mr. White
______________________________

1/1/16 foreign inv no. 25/0/150/0
1/2/16 domes inv no. 30/50/0/150
1/3/16 foreign inv no. 35/0/100/200
1/4/16 domes inv no. 40/70/0/300

And so on.
There is a unique transaction per day for each customer.
Since the correct balance for the first record is in the second record, the balance for the second record is in the third etc. I wonder if it could be a problem of requirying or refreshing. It's only a guess, of course.
After the above specifications, please let me know if can anyway try out the queries you posted.
David

[David57]

I have noticed that after entering a value in the field DomesticInv (or ForeingInv) of the form F_Ledger and typing again the same value upon the previous one (same row, same field) the balance shown is absolutely correct.
This is rather queer and interesting. If we can get the code to perform the same thing, it could perhaps solve the problem.
Any idea?
David

[PhilOfWalton]

Sorry, David, one or other or both of us seem to be muddled.

Could you again state clearly possibly with a bit more data, exactly what you want as input and exactly what you want as output.

Normally, if there is only 1 entry, a running balance would be simply sgow the 1 entry.

With multiple entries, the running balance would follow the same pattern and include all previous entries and the current entry.

Phil

[David57]

Sorry, Phil. You are right, though the only one to be muddled for sure is me, I think. Thank you for your patience, anyway.
I’ll try to explain what I need.
I have two tables TblLedger and TblCustomers and a form, F_Ledger based on Customers. This is why CstName in TblLedger
Is a Lookup field. A relationship between the two tables has been established and referential integrity enforced.
Now, when I call customer Mr. White and open F_Ledger, my input data are:
Date = date of invoice
Description: = type and # of invoice
DomesticInv: = (default value 0) amount of invoice (if Mr. White is placing an order to be shipped within the country)
ForeingInv: (default value 0) amount of invoice (if Mr. White is placing an order to be shipped abroad)
Balance: for each record this field should show, up to the last transaction recorded, the sum of the entire field DomesticInv and the entire field ForeignInv.
As it is, the balance shown (last column) is:

1/1/16-foreign inv no. 25-0/150/0
1/2/16-domes inv no. 30-50/0/150
1/3/16-foreign inv no. 35-0/100/200
1/4/16-domes inv no. 40-70/0/300

While it should be:

1/1/16-foreign inv no. 25-0/150/150
1/2/16-domes inv no. 30-50/0/200
1/3/16-foreign inv no. 35-0/100/300
1/4/16-domes inv no. 40-70/0/370


As you can see, the balances shown in this first example are offset just by one record.

David

[PhilOfWalton]

I believe the 2 queries I sent you both gave the required result.

You probably need a subform on your Customer form to display them.

As I mentioned, I loathe Lookups, as information is partly hidden, but that's up to you. The queries were written on the basis of using IDTableCustomers as part of the criteria, and again, you will find this method much easier for linking your main Customer form to the new subform.

Phil

[David57]

Thanks for your help Phil.
I have seen the result of the query attached to your last post and it looks ok, it is precisely what I was looking for.
Now, as I told you before, I am working with F_Ledger where I type my data in input and where the balance (output) is also displayed. If I want F_Ledger to display the correct balance values and If I understand what you mean, I should base F_Ledger both on your query and on TblCustomer. Is this right?

[PhilOfWalton]

I am not sure of your level of expertise with Access, but the user should never be able to see tables, and only very very rarely see queries. Data should always be input using forms, and viewed either using forms or reports.

So yo need to create a form based on your customer table and a subform based on one or other of the queries I sent you.

The LinkMasterFields & LinkChildFields will be IDTblCustomers which is why I suggested using the table structure in my earlier post.

The subform is updateable, so as you add new records, the correct balance should appear.

You may need to add additional criteria to those queries, as the balance will be accumulated over all time, and I have a feeling you may need that balance monthly. Difficult to know as in my example, I show the dates with the month spelt out, and in your example I don't know whether you are using British Date format or US Date format.

Phil