Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / January 2008

Tip: Looking for answers? Try searching our database.

modify value in table in large amount.

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
fox - 31 Jan 2008 15:36 GMT
Hi!
   I have a question. I have a large data table like this:
Item#      value       quantity     cost
  1             2               3            6
  2             ..               ..            ..
and so on......
Now the quantity value is changed already, but the cost is still old value.
What is the best way to make it recalculate the cost? Use a Form with VB? Or
I shoud write a Module?
Thank you very much.

Fox
KARL DEWEY - 31 Jan 2008 16:27 GMT
First you should not be storing calculated data as parts of the calculation
are subject to change and then the results will be in error as you are now.  
Do to calculations in a query ot form as needed.

Second you need to backup your database.  

Third create an update query and use [value] * [quantity]  in the Update To
row of the query design view grid.  
Signature

KARL DEWEY
Build a little - Test a little

> Hi!
>     I have a question. I have a large data table like this:
[quoted text clipped - 8 lines]
>
> Fox
John W. Vinson - 31 Jan 2008 16:59 GMT
>Hi!
>    I have a question. I have a large data table like this:
[quoted text clipped - 8 lines]
>
>Fox

The best way to deal with this is to not store the Cost in the table AT ALL.

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]
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.