A "balance" field that gets data from previous entries?

[sirkistova]

Hello,
I am writing a VB-Access application for cleaning services.
There are Customers table and Cleaners table. The customers pay for cleaning services. The cleaners work and get paid.
There is Cleanings table – every cleaning session that took place has an entry in this table.
There is payments table – every payment that took place has an entry in this table.
I need to implement a screen, which shows, for a specific customer, the history of cleanings and payments and balance. I implemented it with a (union) query and a table screen, but I don't know how to implement the "balance" column.
"Balance" column summarizes information from previous entries.
I guess there should be defined a certain date/time to begin from, when the balance was zero.
Is it possible with SQL query, to implement a field that gets data from previous rows of the query?
Alternatively, is it possible to write VBA code that adds into a column in a table screen that gets the table contents from a query?
Any other idea?
Thanks!
Tova

[RhinoBull]

I don't know how your Cleaners table is implemented but I would at least have:

- reciept_id
- total_amount
- paid_amount

so getting the remaining_balance would be as ssimple as this:

select (total_amount - paid_amount) as remaining_balance from Cleaners where reciept_id = 123;

[sirkistova]

OK so I have remaining_balance which is the balance of 1 day. (or 1 transaction). But I would like to also have a field that summarizes the field remaining_balance over many rows, and displays it for each row.
I hope it is possible. (Perhaps with the use of "sum"?)
Thanks
Tova

[RhinoBull]

How helpful could total_remaining_balance be if it runs across multiple transaction (same customer I suppose?)?

In any case you can execute sql similar to one below:

select (Sum(total_amount) - Sum(paid_amount)) as total_remaining_balance
from Cleaners
where customer_id = 12345

--and if you want break kit down by reciept_it then add the following line as well:

group by receipt_id

[sirkistova]

I'll try to rephrase my question.
Let's assume there is a table with 1 field - IntegerNumber.
I need to write a query whose result is the same length as the table, aka same number of rows, and there are 2 fields in the result of the query: The integer number (that's easy, just "select" it), and, for each row, the sum of all the integer numbers from the beginning of the table and until this row.
For example, if the table is:

2
4
1
1
10

I need a query whose result is:
2 2
4 6
1 7
1 8
10 18

I hope I am better understood now.
Thanks
Tova

[RhinoBull]

I hope I am better understood now.

Unfortunately your last post is as clear as mud...
Maybe you can provide us with exact table design and perhaps sample data from table - providing plain numbers without explaining what they represent is not going to work.

[sirkistova]

Hello Rhino Bull,

Your first relpy gives field: remaining_balance
That is the 1st field that I need. Good.

Your second reply gives field: total_remaining_balance
This is the 2nd field that I need. But I need it for *every* remaining_balance.
Sum up ALL remaining_balances from the beginning and until the current one, for each remaining_balance. For each row with no grouping of rows that causes fewer rows in the result of the query.
For each row, sum-up the remaining_balance from this row and the total_remaining_balance from the previous raw, to calculate total_remaining_balance of this row, is what I need.

I'm afraid this is not possible with a query.
Perhaps what I need to do is calculate all remaining_balances and save in a temporary table. Then with VB code (not query) calculate all total_remaining_balances and append to every row in the table. Then display.
Thanks
Tova

[incidentals]

I would normally create a sub that walked through such a data structure adding the contents of the new field as it went

the pure sql version would i think be a little clumbsy as you need to generate the separate values for each record as you find them

hope that helps (more if needed)

[sirkistova]

OK I implemented it with a temp table.
1. A query finds out balance and writes to a temp table.
2. A sub calculates total_balance and appends to the temp table.
3. Display.
Thanks
Tova: