updating one column in two tables

  • Thread starter Thread starter rudwan
  • Start date Start date
R

rudwan

who i can make a link between two tables through one
complex column ,
my file has two tables , one column of the first table is
depend of one column in the second table ,
i want the same change which will happened to the first
table to be automatically updated to the second table
 
Rudwan

You did not describe your table structure, nor the business reason you have
chosen to store the same data in two tables. Generally, there's not a need
for such redundant data storage.

More info, please...

Jeff Boyce
<Access MVP>
 
Access does not support Triggers (which is what you are
looking for). The alternative is if you are not using
bound recordsets, and are actually updating the data
through action queries,(insert,update, delete), you can
call this query in a procedure that updates both tables at
the same time.

I suspect you are asking too much of Access, Or you need
to design your database differently.

Or if you are using your keys properly, and you update one
record, the update can propagate by cascading update and
delete.
 
I am handling the telephone bills in my company, each bill
consist of telephone no - account no, it is possible that
one account number has one or more than telephone number,
opposite is not true.
Telecom Company will issue the bills like this example:
Account no -----telephone no----------sub amount
12345678---------45696358-------------- 1500
69538459---------------2500
36215935---------------3000
-----------------------------------------------------------
---totall bill for account no (12345678) is 7000.
For payment purposes I need to arrange separate table for
accounts, and for analysis purposes I have to arrange
separate table for telephone no as details:
1-accounts table : will consist of : accountID-account no -
some details .
2-telephone table : will consist of : telephoneID-
telephone no-account no - some details ( relationship
with account table on account no )
3-Bill Account : BillID-account no-amount-date-some other
codes
4-Bill Telephone : Bill No-telephone no-amount-date
Program Requirement:
1-. When I will receive the bill I will enter its data in
(bill telephone table)-and automatically summary of it
should transfer to (bill account) table.
2-suppose I want to make any changes in (bill telephone
table) as insert-delete-update, I want the same effect to
be apply to (bill account) table.
This operation can be done be making group by query from
bill telephone table, but I don’t like to use a grouping
query, because it will not allow me to make any changes to
it. (Account bill) table will be only the raw data, which
I will deal with it to add some marks late on for bill
processing like (receive-check-under payment-paid already …
etc).
 
Rudwan

You may wish to review the topic of "normalization".

From your description, I'm inferring that you are trying to save calculated
values (account total). A review of this newsgroup's posts (tabledbdesign)
will reveal a strong consensus against doing that.

It sounds like a description of your "world" has an Account (?tblAccount,
with related descriptive fields), and has bills (?tblBilling) that include a
phone number, an amount, a billing date and the account number.

To get an "Account Total" for any particular billing date (or date range),
you can simply create a "totals" query.

Or have I misunderstood what you're trying to do...?

Good luck

Jeff Boyce
<Access MVP>
 
Thanks alot mr Jeff for your concern
i think you got the point , yes i want summary amount for
account , that could be derived easly by makking a
groupnig query , it will give the exact result ,
but the problem is i want to add additional data to the
query after posting its details , but a grouping query
will not allowed me to add any changes ,
because my procedure is to pay the bills step by step
first step recieveg bill . then recording its
informations , then payment , then makking the monthly
accountin entry ,
for each step i have to put a flag to each step , who i
can do that ?
 
Rudwan

I still do not completely understand your data structure, so these are only
possible ideas.

It sounds like you are trying to do your work in a query. Are you using
forms? They will provide you with much greater control, and could allow you
to display the account total in a main form, and the bill(s) and monthly
payments in sub-forms.

Good luck

Jeff Boyce
<Access MVP>
 
dear jeef
sorry for delay , but i changed my floowchart for my file
i alreay use form ( for accounts ) < and sub form ( for
bill details ) <
total amount of subform , will be the amount value in the
main form ( for one bill )
now who i can update the filed ( amount ) of main form
to be changeble according to any change may happened in
the sub form ( asub_amount ) only .
 
Back
Top