use update query in a table of one-many

B

balu

dear friends.
i want to use the update query in a table from another table to get up date
the table like this
Table1:
field employeeid
" billnumber(pk)
" date
" paymentsmade
" deductions
Table2
field " billnumber
" paymentid(pk)
" paymentmade
" deductions
i want to update Table1.paymentsmade , and table1.deductions to
sum(table2.paymentmade) and table1.deductions to sum(table2.deductions)
is it possible kindly advise please
 
J

John W. Vinson

i want to update Table1.paymentsmade , and table1.deductions to
sum(table2.paymentmade) and table1.deductions to sum(table2.deductions)
is it possible kindly advise please

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.

John W. Vinson [MVP]
 
B

balu

thanks dear vinson
can u please help me one more thing please for which im looking over whole
news group!
(1) i want to export many querys to excel using outputto method ofcourse
its doing the job but its creating an new work sheet for every query
how to put all data into one work sheet
(2) or else open an excel file and import all the necessary table and do
canculations but
(A) by this method how to use date criteria for importing data from tables
(b) how to access such excel files which will be in the my documents folder
on click event and come back to access again
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top