solution to table

[cliff]

I am bit new to access, trying best learn. I have small problem :


My one of investment like this :
Invenstment a: Investment B
deposit date 1/01/2001 1/6/2006
amount 25000/- 30,000/-
period 3 years 10 year
maturity date 1/01/2004 1/4/2016

above deposit amount reinvested for further 2 years , on maturity I have
withdrawn 5000/- +interest and balance Rs.20,000/- reinvested for 6 months,
again on maturity I have withdrawn 8000/- and balance Rs.12,000/- + interest
reinvested for 3 years. In between I lost track of initial investment and
source etc. Now I want prepare report somelike this

date Particulars dr cr balance
1/01/2001 Trf from sb 41 25000 25000
1/01/2004 Interest 1000 26000
1/01/2006 Interest 500 26500
1/01/2006 withdrawal 6500/- 20000
1/07/2006 Interest 200 20200
1/7/2006 withdrawal 8000/- 12200

current Balance 12200/-

further I want tabular statement something like this :-

particulars 2001 2004 2005
2006

Investment A 25,000/-
Interest earned 1000/- -
700/-
total amount withdrawan -
14,500/--
balance 25,000/- 26000/ - -
12,200/-

Investmet B
30,000/-

total balance in all funds
42,200/-


Please help me out to solve this problem

thanks in advance
cliff

 

[Lord Kelvan]

ummm ... where are you storing the information on how much you have
widthdrawn apart from in your memory

to do what you want you need the date of the initial investment
information which you seem to have BUT you also need the date of each
reinvestment.

You also need the value of intrest and its period ie 6% per year over
3 years or 6% after 3 years.

and you also need how much was widthdrawn and when it was
widthdrawn.

if you have merely altered the ammounts in the table storing your
investments then that information is lost. i am presuming you are new
to databses entirly not just access and my question is do you have
multiple tables linked together to store all that relevant
informaiton. because if you dont then there aitn much you can do
without rebuildign your databse to have all that information. then
you can hopefully do a balance sheet look alike.

pelase tell me what info you have in the database and how it appears

Regards
Kelvan

 

[John W. Vinson]

I am bit new to access, trying best learn. I have small problem :


My one of investment like this :
Invenstment a: Investment B
deposit date 1/01/2001 1/6/2006
amount 25000/- 30,000/-
period 3 years 10 year
maturity date 1/01/2004 1/4/2016

Do you have a separate FIELD for each investment? In that case... ouch. Your
database design *IS WRONG*. "Fields are expensive, records are cheap"!

above deposit amount reinvested for further 2 years , on maturity I have
withdrawn 5000/- +interest and balance Rs.20,000/- reinvested for 6 months,
again on maturity I have withdrawn 8000/- and balance Rs.12,000/- + interest
reinvested for 3 years. In between I lost track of initial investment and
source etc. Now I want prepare report somelike this

If that information does not exist in your database, how on Earth would you
expect it to be recoverable?

date Particulars dr cr balance
1/01/2001 Trf from sb 41 25000 25000
1/01/2004 Interest 1000 26000
1/01/2006 Interest 500 26500
1/01/2006 withdrawal 6500/- 20000
1/07/2006 Interest 200 20200
1/7/2006 withdrawal 8000/- 12200

current Balance 12200/-

further I want tabular statement something like this :-

particulars 2001 2004 2005
2006

Investment A 25,000/-
Interest earned 1000/- -
700/-
total amount withdrawan -
14,500/--
balance 25,000/- 26000/ - -
12,200/-

Investmet B
30,000/-

total balance in all funds
42,200/-

This would be a Report, probably based on a Crosstab query.

You might want to consider purchasing one of the many good financial
management commercial programs rather than trying to reinvent one from
scratch.

 

[cliff]

Hi lord klevan

yes i am new to structured databases.

I have following tables

deposit table
date
accountno
receiptno
amount
rate
period

interest table
date
receiptno
amount

withdrawal table
date
receipt
amount

now I want you to help me move further


thanks
cliffs

 

[Lord Kelvan]

ok i am guessing the recipt no is the field that connects the three
tables together ie

reciptno 1 will be releated to the initial deposit and will be
releated to all inteest payments in the intrest table and in the
widthdraw table

ie


deposit table
date xx/xx/xxxx
accountno xxxxxxxx
receiptno 1
amount 200000
rate 5%
period 3


interest table
date xx/xx/xxxx
receiptno 1
amount 1000

date xx/xx/xxxx
receiptno 1
amount 200

withdrawal table
date xx/xx/xxxx
receipt 1
amount 5000

date xx/xx/xxxx
receipt 1
amount 6000

if this is correct then it can be done if not then it still cannot be
done because based on that there is no value linking thoes tables
together.

what you need is

deposit table
depositid (primary key)
date
accountno
receiptno
amount
rate
period


interest table
intrestid (primary key)
depositid (forgien key)
date
receiptno
amount


withdrawal table
withdrawid (primary key)
depositid (forgien key)
date
receipt
amount


you also shouldnt use date as a field name

so could you please tell me how these tables are linked together so
that you know the valeus in withdrawal and intrest are linked to the
record in deposit

Regards
Kelvan

 

[Lord Kelvan]

also could you tell me what the data types for each of theos fields in
the three tables are woudl be quite helpful

 

[cliff]

Hi "Lord kelvan" thanks for your help. I want to join this table based all
interest amount to be added to respective accountno and withdrawal to
deduct from concerned accountno and report by say quarterly,half yearly etc

I feel my datatype for amounts,rate,period - numbers
dates - dates and
others - text


Kindly help to solve this

thanks