How do you put a previous balance in a textbox?

[DogLover2006]

Hello,
Here is what I am trying to do. I have two table one with just a previous balance for one month. The other table has a credit and debit totals by days. I only want to use the previous balance for the first record that starts that month and then I want to subtract the debit amount from it to get a new previous balance that the next day will either subtract the debit or add the credit to get next balance for the next day and so on. Any Ideas? I even try to do a query but no luck at that either. Here is example:
date 1/3/07
preBalance = 1600.25
debit Amount = 15.25
what I want is (1600.25 - 15.25 = 1599.00)
date 1/3/07
credit amount = 300.00
(1599.00 + 300.00 = 1899.00)
and so on for each date line by line
Thank you for all your help

 

[ZmrAbdulla]

You have aggregate function like
DLast(), DSum() and DLookUp()

See the help files for more info.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[DogLover2006]

ZmrAbdulla,

I am sorry I forgot to say the prevBalance is record 1 and the Debit amount or Cedit amount is on the next record. So I want the next record to take the prevBalance - Debit amount or + the Credit amount.

 

[ZmrAbdulla]

Can you show some sample data?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Take the first step in faith. You don't have to see the whole staircase, just take the first step.
(Dr. Martin Luther King Jr.)

 

[danvlas]

Not sure it will help, but this is what I do:

I have two dates that default to the beginning and end of current month. The dates can be changed by user.

Have 5 queries:

1 query for all products/articles

2 query to obtain the 'stock': all entries for date<[beginning date] minus all exits for date<[beginning date]. This returns the INITIAL BALANCE

3 query to obtain all entries for the period

4 query to obtain all exits for the period

5 Left outer join query 1 on all others, using Val(NZ(Quantity,0)) to fake non existing records

The calculations for final balance are made in query 5 ([INITIAL BALANCE] + Val(Nz(Entries,0)) - Val(Nz(Exits,0)).

Has never failed so far...no matter how many time our beloved users changed the primary information...


It is true that for a large number of records it is slower (in Access) than the 'summary table' version, but the accuracy and flexibility pays off big time.

Accuracy is given by the fact that everything is calculation on primary data. Flexibility: you can always add a level of detail to the balance by changing the grouping in queries. No need for structure changes in the summary table.

Moreover, it can give you balances for every day and there is no need for actions like 'close the month', 're-open the month'.

In SQL Server it hasn't yet shown any performance issue for some 1 million records...


HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html