Need changes in Statement to get the correct balance amount ...

[Margaret]

Hi,

The following code works accurate when I don't care about the Date Order.

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE SD2.ID <= SD1.ID AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, ID", db

But I need all the dates in a particular order and the problem is that sometimes I need to enter data with old dates as well. The following code doesn't work properly showing wrong values in "Balance" Column. How to solve the problem? Please help me out.

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE SD2.DDate <= SD1.DDate AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, DDate", db

The attached file may help to understand my problem.

Regards,

Margaret

[tomy_3003]

Hi...
If u r using oracle database then use rowid in place of date... that'll solve your problem.

But if u r using MS Access database then I think u have to modify your table because in Access there is no such rowid field. U have to add another column in the table and set data type of that field as 'AutoNumber'. Now if u use this field in place of the date field your problem will be solved.

[Margaret]

Hi...
If u r using oracle database then use rowid in place of date... that'll solve your problem.

But if u r using MS Access database then I think u have to modify your table because in Access there is no such rowid field. U have to add another column in the table and set data type of that field as 'AutoNumber'. Now if u use this field in place of the date field your problem will be solved.

Hi,

I am using the MS Access database. There is a field called "ID" with autonumber. How to solve the problem now?

Regards,

Margaret

[tomy_3003]

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE SD2.ID <= SD1.ID AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, DDate", db

[Margaret]

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE SD2.ID <= SD1.ID AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, DDate", db

Hi,

I have tried the above code but it shows wrong BALANCE amount.

Regards,

Margaret

[tomy_3003]

Hi..

Actually the output is not wrong. What is happening here is that the output is coming in reverse order because u have sorted the output as reverse order. I think in your table the date and id field are sorted opposite to each other.

[Margaret]

Hi..

Actually the output is not wrong. What is happening here is that the output is coming in reverse order because u have sorted the output as reverse order. I think in your table the date and id field are sorted opposite to each other.

Hi, I have not sorted anything in the Database table. Just using the querry only. How to solve the problem now?

Regards,

Margaret

[tomy_3003]

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE (SD2.DDate < SD1.DDate OR (SD2.DDate = SD1.DDate AND SD2.ID <= SD1.ID)) AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, DDate", db

[Margaret]

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone, Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2 WHERE (SD2.DDate < SD1.DDate OR (SD2.DDate = SD1.DDate AND SD2.ID <= SD1.ID)) AND SD2.CustomerName= SD1.CustomerName) As Balance FROM SalesData SD1 Where CustomerName like '%" & txtSearchAnyWord & "%'ORDER BY CustomerName, DDate", db

Now, I get the following output which is also incorrect.

Margaret

[tomy_3003]

Can u please send me the data from your table for this particular customer....

Copy the data from access table and paste in in an excel file and then attach that excel file. Without seeing the table i can't find the problem... I created a dummy table and this code is working fine in my table.

[leinad31]

Because you used LIKE to specify the customer ... so what happens is debits and credits from customers with similar names are also included in the running balance.

[Margaret]

Because you used LIKE to specify the customer ... so what happens is debits and credits from customers with similar names are also included in the running balance.

Hi, It shows the exact CODE (Customer Code) as it is in the last post which determines that it retrieved the data for the same customer not other customers whose name is similar.

Regards,

Margaret

[leinad31]

Check that there are no nulls in the table for debits/credits... and set the relevant fields to default to 0 rather than null to ensure that subtraction can be performed on all rows (can't subtract null).

[szlamany]

When you post your SQL string would you mind, please, putting a couple of CARRIAGE RETURNS in the string so that it's not 3 feet wide!

It's impossible to look at and understand when you cannot see the whole string in one screen view!

[Margaret]

Check that there are no nulls in the table for debits/credits... and set the relevant fields to default to 0 rather than null to ensure that subtraction can be performed on all rows (can't subtract null).

I am using the following code (to set the format in MSHFlexGrid to show):

Dim Y As Long
    With MSHFlexGrid1
    For Y = .FixedRows To .Rows - 1
        .TextMatrix(Y, 8) = Format(.TextMatrix(Y, 8), ("#,##.00;;\- "))
        Next Y
        End With

Regards,

Margaret

[Margaret]

When you post your SQL string would you mind, please, putting a couple of CARRIAGE RETURNS in the string so that it's not 3 feet wide!

It's impossible to look at and understand when you cannot see the whole string in one screen view!

Yes, you are correct. But I don't know how to cut it into small pieces/lines.

Margaret

[szlamany]

First of all - you are giving us the SQL statement inside a VB string - we really just need to discuss the SQL statement...

But at any rate...

rs.Open "SELECT CustomerName, Option, FileNumber, City, Telephone" _
      & ", Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit" _
      & ",(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2" _
      & " WHERE SD2.DDate <= SD1.DDate AND SD2.CustomerName=" _
      & " SD1.CustomerName) As Balance FROM SalesData SD1" _
      & " Where CustomerName like '%" & txtSearchAnyWord _
      & "%'ORDER BY CustomerName, DDate", db

but even better like this...

SELECT CustomerName, Option, FileNumber, City, Telephone
          , Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit
          ,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2
                 WHERE SD2.DDate <= SD1.DDate AND SD2.CustomerName=
                              SD1.CustomerName) As Balance
            FROM SalesData SD1
                  Where CustomerName like '%SMITH%'
                       ORDER BY CustomerName

[Margaret]

Where CustomerName like '%SMITH%'

Hi, there are more than 150 customers in the database and I need to select any customer from the database to get the statement. So, I put one textbox with name txtSearchAnyWord.

Margaret

[szlamany]

That was just an example - we don't need to battle the "build a SQL string in VB issues" when trying to figure out why a query does not work.

What is wrong with this query:

SELECT CustomerName, Option, FileNumber, City, Telephone
          , Fax, DDate, RP, InvoiceAmount As Credit, AmountReceived As Debit
          ,(SELECT SUM(InvoiceAmount-AmountReceived) FROM SalesData SD2
                 WHERE SD2.DDate <= SD1.DDate AND SD2.CustomerName=
                              SD1.CustomerName) As Balance
            FROM SalesData SD1
                  Where CustomerName like '%SMITH%'
                       ORDER BY CustomerName

Obviously change the NAME to one that is in your database.

Does it not return a BALANCE that you expect?

As indicated by LEINAD31 if either the InvoiceAmount or the AmountReceived is null in any record for a given customer then that SELECT SUM()... will be wrong.

Did you see if any of these amounts are null?

Answer these two questions and post back the query you used most recently that did not work.

[leinad31]

Its not advisable to use date for determining the running balance, rows on the same end day will be included in the calculation when they should be excluded cause they follow the row whose running balance is to be determined. That's why we used autonumber ID as the basis for the running balance, since its unique and indicates the order of the addition of the records... which raises another point, your view should follow the same ascending order as the ID.

Based on post number 9, your ordering by description then by date, the IDs are in jumbled order... so the running balance based on ID will give the wrong result.

[Margaret]

Its not advisable to use date for determining the running balance, rows on the same end day will be included in the calculation when they should be excluded cause they follow the row whose running balance is to be determined. That's why we used autonumber ID as the basis for the running balance, since its unique and indicates the order of the addition of the records... which raises another point, your view should follow the same ascending order as the ID.

Based on post number 9, your ordering by description then by date, the IDs are in jumbled order... so the running balance based on ID will give the wrong result.

Hi,

Thanks a lot for your advice. Finally, what should be the right code that I have to write now (Cause I have modified it so many times).

Regards,

Margaret

[leinad31]

Quickest fix would be to display the records ordered on ID... if you will follow description, code, etc sort then you will have to implement the running balance progmatically ...which also means you will have to get the starting balance based on the lowest ID, or total balance for IDs before lowest ID (note: not first ID in returned recordset sorted by description, etc), in the returned recordset, then iterate through the grid/listview control and do arithmetic on the debits/credits to get next balance.

[Margaret]

Quickest fix would be to display the records ordered on ID... if you will follow description, code, etc sort then you will have to implement the running balance progmatically ...which also means you will have to get the starting balance based on the lowest ID, or total balance for IDs before lowest ID (note: not first ID in returned recordset sorted by description, etc), in the returned recordset, then iterate through the grid/listview control and do arithmetic on the debits/credits to get next balance.

Thanks a lot for your usual cooperation. Since I am a learner, still have confusion which code to follow.

Once again thanks for the help.

Regards,

Margaret

[leinad31]

Note that the subquery for subtotal works on the assumption that the records it returns are the records before and up to the parent query's current record. The subquery and its parent query will have to use the same "yardstick" for the running balance to work. If the subquery is based on order of ID's while its parent query orders by description, code then you have different yardsticks or it doesn't work regardless of the condition in your WHERE clause.

Imagine your earning 200 a day from sunday to saturday, getting running balance based on ID is similar to running balance based on weekday (or better day in year); 400 on day 2, 600 on day 3 etc. If the parent query sorted the days alphabetically it will start on Friday day 6. Instead of running balance 200 for friday as first day, the subquery based on yearday will return 1200.

It gets more complicated as you go father away from the order based on unique, ordinal (I HAVE to stress that so you won't accidentally base running balance thru query on non-unique, non-ordinal fields), autonumbered ID.

So either display records based on common yardstick ID and not by description, code, etc fields. Or calculate the running balance using VB code at run-time (not thru query) after loading the debits/credits in the grid-like control, you will also need the starting balance based on earliest ID in returned recordset.

[Margaret]

Note that the subquery for subtotal works on the assumption that the records it returns are the records before and up to the parent query's current record. The subquery and its parent query will have to use the same "yardstick" for the running balance to work. If the subquery is based on order of ID's while its parent query orders by description, code then you have different yardsticks or it doesn't work regardless of the condition in your WHERE clause.

Imagine your earning 200 a day from sunday to saturday, getting running balance based on ID is similar to running balance based on weekday (or better day in year); 400 on day 2, 600 on day 3 etc. If the parent query sorted the days alphabetically it will start on Friday day 6. Instead of running balance 200 for friday as first day, the subquery based on yearday will return 1200.

It gets more complicated as you go father away from the order based on unique, ordinal (I HAVE to stress that so you won't accidentally base running balance thru query on non-unique, non-ordinal fields), autonumbered ID.

So either display records based on common yardstick ID and not by description, code, etc fields. Or calculate the running balance using VB code at run-time (not thru query) after loading the debits/credits in the grid-like control, you will also need the starting balance based on earliest ID in returned recordset.

Hi,

Thanks a lot for your detailed description.

With best regards,

Margaret