How do I decriment each row as the $$ is used?

  • Thread starter Thread starter M Skabialka
  • Start date Start date
M

M Skabialka

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
 
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.
 
Responses in line.
--
Marsh
MVP [MS Access]



M said:
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?
Click to expand...

Depends on what else you go going on.

Is it done in the query or in the report?
Click to expand...

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.
Click to expand...

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.
 
Back
Top