Hi,

i have got a database which consists of table, queries, forms and reports.
I have one problem now is that I wan to do a query that will be able to bring the figure from another field to the current one.
ok say i have got Ref_No, Event_Date, Balance_BF, Adjustment, Balance.

so if say this is the first event for this particular ref_no (1234) it will bring the amount from another table (Principle_Table) linking the same ref_no (1234) to the Balance_BF fields,
If this is not the first event for this particular ref_no (1234) it will then bring the amount from the previous event Balance field to the current event Balance_BF field.

I am very lost how to do this. Is there a way doing this using query?

This is wat i mean
Ref No Event Date Balance_BF Adjustment Balance
06/00014 31-May-06 115140 -5,757.00 $109,383.00
06/00014 31-Aug-06 109383 -5,757.00 $103,626.00
06/00014 30-Nov-06 103626 -5,757.00 $97,869.00
06/00014 28-Feb-07 97869 -5,757.00 $92,112.00

U can see that the Balance_BF is actually brought down figure from the previous Balance starting from the second events. For the first event, the Balance_BF is actually draw down from another table (Principles_Table)

Is there a way to do this using query?

Hi Derek
I am probably not the right person to answer the question, but I would consider 2 ways to tackle it - depending on how you have set up the tables, relationships and queries.

The most obvious solution is to create a query that calculates whether there is a balance that must be BF for the event. (Search for "Calculating Queries" on this forum.) Use the result of this query to create the next record.

A second thought that I had was:
Do you have a Tbl_BF which stores all the BF values for each Ref?
When a record is updated, check what the outstanding/BF balance is for the Ref and update the table accordingly.

Good luck

Hey thanks thanks. erm ... i got no table for my B/F to be stored ...

Thanks alot anyway for the suggestion.

Anyone can help me on this???

I am being pushed by my boss ...

this looks like a financial app, and i would say general it would be bad practice to store the bfs, other than at the start of the accounting period - year /maybe month

you need an accounts table to store the initial bfs/ account description/ etc, and a transactions table to store the postings

and you evaluate the balance by adding the postings and opening balance when you need the information

some systems would store a this period transactions in the master record, but theres no real need

this looks like a financial app, and i would say general it would be bad practice to store the bfs, other than at the start of the accounting period - year /maybe month

you need an accounts table to store the initial bfs/ account description/ etc, and a transactions table to store the postings

and you evaluate the balance by adding the postings and opening balance when you need the information

some systems would store a this period transactions in the master record, but theres no real need

hi hi ... so sorry i dun really get wat u mean ... anyway yes this is a financial application ... i am having headaches now ... a very big big headache