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
|
|||
|
|||
How do I decriment each row as the $$ is used?
I need to create a new report which does some accounting functions.
The first line in the report shows the $$ available, then each subsequent line will show some of it used, and a balance of $$ left over. I think I need a running sum or something, but I have only used that for counting records before. Allocated Used Balance 1/1/04 1000.00 1000.00 2/2/04 Paper 25.00 975.00 3/3/04 Pens 100.00 875.00 etc I am not keeping the balance stored in the table, preferrring to calculate it on the run. What is the best way to accomplish this? Is it done in the query or in the report? I also want to show the same information on the data entry form, showing continuous forms with each row showing the balance as $$ is spent. Thanks, Mich |
#2
|
|||
|
|||
How do I decriment each row as the $$ is used?
Add a text box to the Detail section of your report, and give it these
properties: Name UsedRS ControlSource Used Running Sum Over Group Visible No This text box gives you a total for the amount used so far for the group. Now add another text box with these properties: ControlSource =[Allocated] - [UsedRS] Format Currency This text box shows the difference between the amount allocated for the group, and the running sum of the amount used so far. -- 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. "M Skabialka" wrote in message ... I need to create a new report which does some accounting functions. The first line in the report shows the $$ available, then each subsequent line will show some of it used, and a balance of $$ left over. I think I need a running sum or something, but I have only used that for counting records before. Allocated Used Balance 1/1/04 1000.00 1000.00 2/2/04 Paper 25.00 975.00 3/3/04 Pens 100.00 875.00 etc I am not keeping the balance stored in the table, preferrring to calculate it on the run. What is the best way to accomplish this? Is it done in the query or in the report? I also want to show the same information on the data entry form, showing continuous forms with each row showing the balance as $$ is spent. Thanks, Mich |
#3
|
|||
|
|||
How do I decriment each row as the $$ is used?
Responses in line.
-- Marsh MVP [MS Access] M Skabialka wrote: I need to create a new report which does some accounting functions. The first line in the report shows the $$ available, then each subsequent line will show some of it used, and a balance of $$ left over. I think I need a running sum or something, but I have only used that for counting records before. Allocated Used Balance 1/1/04 1000.00 1000.00 2/2/04 Paper 25.00 975.00 3/3/04 Pens 100.00 875.00 etc I am not keeping the balance stored in the table, preferrring to calculate it on the run. Good! What is the best way to accomplish this? Depends on what else you go going on. Is it done in the query or in the report? It's pretty easy to do in the report using a RunningSum text box. Add a text box named txtRunUsed and set its expression to =[Used] and RunningSum to Over All (or Over Group if you're grouping the report). Then the running balance can be displayed in another text box with the expression =Allocated - txtRunUsed But since you want to do the same thing in a continuous form, it may (or may not) be better to use the same approach (query) in both places. Depending on what else you have going on in the report, using this a query with a subquery may make the report balk at the complexity of this approach. I also want to show the same information on the data entry form, showing continuous forms with each row showing the balance as $$ is spent. Since there is no RunningSum feature for forms, you will need to use a query to calculate the balance. Set the form's RecordSource to a query that looks something like: SELECT table.transdate, table.Allocated, table.itemname, table.used, (SELECT Sum(X.used) FROM table As X WHERE X.transdate = table.transdate ) As Balance FROM table Then you can display the Balance field in a text box on the form. Remember that whenever a new record is added to the form or if you allow users to edit the values in an existing record, you have to use a line of code in the form's AfterUpdate event to update the running balance: Me.Requery. |
Thread Tools | |
Display Modes | |
|
|