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
|
|||
|
|||
Add Payment without manually entering the balance
I have a Query / Form set up to deduct a single payment made and the balance
is accurately calculated for the field. I want to be able to enter a payment on a form and a running balance to be maintained. The problem is I have to enter the current balance each time. I need the current balance to be presented in the text field of the Query / Form when a payment is made. I have all the data being saved to a table. Thanks for any help in this matter. |
#2
|
|||
|
|||
Add Payment without manually entering the balance
It's dead easy to display a running balance on a report (using the Running
Sum property of a text box), but not easy to do this reliably in a form. That's because: a) Users can sort the form differently (e.g. on amount not by date) b) Users can filter the form (so not all amounts are shown) c) There may be no strict order for transactions (e.g. where several are on the same date and you aren't recording the time.) d) Even after solving those issues, the form's data will be uneditable (if you use subqueries), or very slow (if you use DSum() etc.) Nevertheless, it's important NOT to store the balance, so you don't compromise the integrity of your database. (For example, if someone goes back and adds an old transaction later, every entry since then will be wrong.) Simplest answer: use a report to get the running balance. Alternatives: subquery: http://allenbrowne.com/subquery-01.html or DSum() expression: http://allenbrowne.com/casu-07.html (DSum() doesn't reflect the current record until it is saved.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "NDClark" wrote in message ... I have a Query / Form set up to deduct a single payment made and the balance is accurately calculated for the field. I want to be able to enter a payment on a form and a running balance to be maintained. The problem is I have to enter the current balance each time. I need the current balance to be presented in the text field of the Query / Form when a payment is made. I have all the data being saved to a table. Thanks for any help in this matter. |
#3
|
|||
|
|||
Add Payment without manually entering the balance
Thanks Allen. I am still not sure how best to display this data. I do
understand though what not to do. Forms are not the way to go with what I am trying to do. "Allen Browne" wrote: It's dead easy to display a running balance on a report (using the Running Sum property of a text box), but not easy to do this reliably in a form. That's because: a) Users can sort the form differently (e.g. on amount not by date) b) Users can filter the form (so not all amounts are shown) c) There may be no strict order for transactions (e.g. where several are on the same date and you aren't recording the time.) d) Even after solving those issues, the form's data will be uneditable (if you use subqueries), or very slow (if you use DSum() etc.) Nevertheless, it's important NOT to store the balance, so you don't compromise the integrity of your database. (For example, if someone goes back and adds an old transaction later, every entry since then will be wrong.) Simplest answer: use a report to get the running balance. Alternatives: subquery: http://allenbrowne.com/subquery-01.html or DSum() expression: http://allenbrowne.com/casu-07.html (DSum() doesn't reflect the current record until it is saved.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "NDClark" wrote in message ... I have a Query / Form set up to deduct a single payment made and the balance is accurately calculated for the field. I want to be able to enter a payment on a form and a running balance to be maintained. The problem is I have to enter the current balance each time. I need the current balance to be presented in the text field of the Query / Form when a payment is made. I have all the data being saved to a table. Thanks for any help in this matter. . |
#4
|
|||
|
|||
Add Payment without manually entering the balance
To do it with a report:
1. Create a query that contains the fields from the tables you want (assuming there's more than one table involved.) 2. Create a report based on this query. 3. Put the fields you want onto the report. Add an extra text box to display the running sum, and set its Running Sum property (on the Data tab of the properties box) to Over Group (or Over All.) Depending how your tables are set up, it may be more involved. The example above would work if you had ONE field with positive and negative values for credits and debits. If you have 2 fields named Due and Paid, you would type an expression into the Field row in query design: IIf([Due] Is Null, 0, -[Due]) + IIf([Paid] Is Null, 0, [Paid]) You now have the field set up the way you need so the running sum works in the report. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "NDClark" wrote in message ... Thanks Allen. I am still not sure how best to display this data. I do understand though what not to do. Forms are not the way to go with what I am trying to do. "Allen Browne" wrote: It's dead easy to display a running balance on a report (using the Running Sum property of a text box), but not easy to do this reliably in a form. That's because: a) Users can sort the form differently (e.g. on amount not by date) b) Users can filter the form (so not all amounts are shown) c) There may be no strict order for transactions (e.g. where several are on the same date and you aren't recording the time.) d) Even after solving those issues, the form's data will be uneditable (if you use subqueries), or very slow (if you use DSum() etc.) Nevertheless, it's important NOT to store the balance, so you don't compromise the integrity of your database. (For example, if someone goes back and adds an old transaction later, every entry since then will be wrong.) Simplest answer: use a report to get the running balance. Alternatives: subquery: http://allenbrowne.com/subquery-01.html or DSum() expression: http://allenbrowne.com/casu-07.html (DSum() doesn't reflect the current record until it is saved.) -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "NDClark" wrote in message ... I have a Query / Form set up to deduct a single payment made and the balance is accurately calculated for the field. I want to be able to enter a payment on a form and a running balance to be maintained. The problem is I have to enter the current balance each time. I need the current balance to be presented in the text field of the Query / Form when a payment is made. I have all the data being saved to a table. Thanks for any help in this matter. . |
Thread Tools | |
Display Modes | |
|
|