need help

[seema]

I want to design a database to keep record of my business. I have three
tables…

1. BANK DETAIL TABLE

BNAME LOAN LIMIT LC LIMIT TOTAL UTILIZED AVAILABLE




2. LC TABLE

S# LC# SUPP BNAME AMOUNT LC TYPE LC TOTAL DATE



Markup No. of Markup Total Mark Up Mark Up Total
Rate Days Per day Markup Paid Balance Princeipal + M/Up




3. IMPORT LOAN TAB LE
S# L/C# DATE supplier Principal Principal Principal
Amount
Paid Balance Date



I have placed a combo box on form and have linked bank name field of table
1 and 2… so that when user makes an entry in table 2 through form it should
first select a bank name from that combo box and then makes further entry..
But what else I want is that, the entry in second table should automatically
fill the same fields in 3rd table and also perform some calculations in third
table. Like, if I enter an amount in table 2 it should be entered in table
three and calculate amount paid, balance amount and so on. Can any one please
help me out that how I can do that?


Thanks.

 

[John W. Vinson]

I have placed a combo box on form and have linked bank name field of table
1 and 2… so that when user makes an entry in table 2 through form it should
first select a bank name from that combo box and then makes further entry..
But what else I want is that, the entry in second table should automatically
fill the same fields in 3rd table and also perform some calculations in third
table. Like, if I enter an amount in table 2 it should be entered in table
three and calculate amount paid, balance amount and so on. Can any one please
help me out that how I can do that?

You're not asking *what* you should do - you're asking *how* you should do it;
and what you're doing is mostly WRONG.

You should NOT be storing the same data redundantly in two tables.

You should also not be storing calculated values, except in very rare cases.
Storing derived data such as this in your table accomplishes three things: it
wastes disk space; it wastes time (almost any calculation will be MUCH faster
than a disk fetch); and most importantly, it risks data corruption. If one of
the underlying fields is subsequently edited, you will have data in your table
WHICH IS WRONG, and no automatic way to detect that fact.

Just redo the calculation whenever you need it, either as a calculated field
in a Query or just as you're now doing it - in the control source of a Form or
a Report textbox.

Take a look at some of the tutorials describing how relational databases
should be constructed at:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

A free tutorial written by Crystal (MS Access MVP):
http://allenbrowne.com/casu-22.html

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials


John W. Vinson [MVP]

 

[Dennis]

I dunno how you guys put up with some of the questions asked, day after day
after DAY. It'd drive me absolutely batty. It never ceases to amaze me how
many bad ways folks come up with to do something database-related. They
should just do a spreadsheet and keep their data that way.

*sigh*

 

[John W. Vinson]

I dunno how you guys put up with some of the questions asked, day after day
after DAY. It'd drive me absolutely batty. It never ceases to amaze me how
many bad ways folks come up with to do something database-related. They
should just do a spreadsheet and keep their data that way.

*sigh*

I'm still waiting to hear from the Vatican about my candidacy for sainthood...
<g>

John W. Vinson [MVP]