By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,426 Members | 3,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,426 IT Pros & Developers. It's quick & easy.

Saving data into a table field by a query calculation

100+
P: 135
Hello,

I have created a query to help me calculate some total values i need for a report.

In the query field i have the following:

TCL1: Costing![Piece Cost ddp]*(Engineering![Level 1 Driver]+Engineering![Level 1 Passenger])

The results are calculated correctly for the report, however now i need this calculated values (from TCL1 on the query) to be stored in a table (COSTING) under the same field name (TCL1).

How can i do this???
Thank you
Sep 12 '07 #1
Share this Question
Share on Google+
8 Replies


Expert 100+
P: 296
Gilberto, storing calculated values is really NOT a good idea. If you're interested why, check out Database Normalisation and Table structures . What is the reason for wanting to do this?
Sep 12 '07 #2

100+
P: 135
Gilberto, storing calculated values is really NOT a good idea. If you're interested why, check out Database Normalisation and Table structures . What is the reason for wanting to do this?
Thnks, i will read that document.

My reason to do this is to be able to easily create new reports and new queries and also so that i have an actual table with "products" where the user could see ALL their characteristics (so far it only stores "#units" and "price", and i would like to also store the calculation "#units * price", which i did with the query).

Thanks again
Sep 13 '07 #3

Expert 100+
P: 296
Thnks, i will read that document.

My reason to do this is to be able to easily create new reports and new queries and also so that i have an actual table with "products" where the user could see ALL their characteristics (so far it only stores "#units" and "price", and i would like to also store the calculation "#units * price", which i did with the query).

Thanks again
Again, Gilberto, it is not a good idea to store calculated values. What if the price changes? You will also have to change all of the totals that were related to that particular price change. In a large database, it could be hundreds or even thousands of changes, where if you don't store the calculated value, you would only need to change the price. Same thing if you change the number of units. It's just bad practice in general.
But...if you are wanting to denormalize your database (which I strongly advise against) then you could always run an Update query. First add the new field to your table, then create the update query.
Sep 13 '07 #4

100+
P: 135
Again, Gilberto, it is not a good idea to store calculated values. What if the price changes? You will also have to change all of the totals that were related to that particular price change. In a large database, it could be hundreds or even thousands of changes, where if you don't store the calculated value, you would only need to change the price. Same thing if you change the number of units. It's just bad practice in general.
But...if you are wanting to denormalize your database (which I strongly advise against) then you could always run an Update query. First add the new field to your table, then create the update query.

Thanks i will definitely take this into consideration and try to figure out another way to achieve what i need.

Thank you for the repliesm
Gilberto
Sep 14 '07 #5

P: 11
Iam Having a similar problem and am trying to think how i can get around this. My scenario is thus; Iam building a DB for a friend who owns a garage. Basically they want a DB that can track all clients/vehicles & warrant of fitness/service Dates which will then lead to emailing / posting reminders to their customers when a new Warrant/Service is Due. I have setup Employee/Client/Vehicle/Job Tables for this purpose and have created relationships and made a form below:

, the [NextWarrantDate] is calculated based on the formula dateadd("m",[WarrantTerm],[NewWarrantDate])and seems to work fine.However I can't seem to get it to update the [VehicleWarrantExp] in the [VehicleDetailsTable] (Highlighted in Green) Can anyone help me to get this to work I have spent 10+ hours reading several 100 pages of text and have now gone partially blind in my left eye , Jokes. Iam determined to somehow get this to work. Once I have Completed this task I need to setup some Queries and then figure out how to send Mass mail via outlook to the Respective Clients which should be even more entertaining VBA yay me.
Thank you
Jun 14 '10 #6

P: 11
@Weise
Appended

Bob Olston from allexperts.com replied.

ANSWER: To reference a control on the Main form from a subform:
Forms!MainFormName!ControlName

YOu could also use

me.parent.controlname
---------------------------------------------------

However

Iam getting a number of errors trying all different ways to implement it
Also I have attached a Second image to maybe help clear things up a little.

In First Image there are 3 Forms , The Main form is ClientForm with a subform named VehicleForm and the 3rd which is a subform within the (VehicleForm) is called JobForm (these are from Left to Right in attached image 1)

ok the VehicleForm contains all relevant data for any registered Vehicle including the vehicles Warrant of Fitness Expiry Date.<< This field/control I need to update Via the JobForm.. Which will Calculate a new Date based on a Date Entered into the NewWarrantDate field in the JobForm.
When a user clicks on the [Update] button I would like the [NewWarrantExpiry] Date to be saved back to the VehicleForm/Table and update the [VehicleWarrantExp] to the new calculated date from the JobForm.



[mistake in image - details below]


[NewWarrantDate] in the [TextBox] in the attached image is supposed to be [NextWarrantDate]Which is actually just a Calculation based on [NewWarrantdate]+[WarrantTerm]in Months using the following input: =Dateadd("m",[WarrantTerm],[NewWarrantDate])
Jun 14 '10 #7

P: 11
Ok I have found the solution Thanks to Bob Olston from allexperts.com.

Bob replied with the following;

As I told you previously, you can refer to a control on a form that is the top level form via this syntax

Forms!>formname>!<controlname>

So if NewWarrantExpiry is a control in the Vehicle form, you can update it via

Forms!Vehicleform!newwarrantexpiry = me.newwarrantdate

Some free Access training is listed here:

http://webpages.charter.net/bobalsto...line_Tutorials

and good bebinner books

http://databases.about.com/od/tutori...ccessbooks.htm


Also Google searches that begin with Microsoft Access xxxxxxxxxxx
or
Microsoft Access vba xxxxxxxxx

Can often get you more immediate help.

HTH

Bob
--------------------------------------------

My Attempt

From this I done the following to update the [VehicleTable].

I opened the [VehicleForm] , then switched to design view.

I Double clicked the [update] button which brings up the ButtonProperties.

under the [events] tab I created a new [Event Proceedure] for the button.

in the subsequent vba dialog I entered the following code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub UpdateWarrant_Click()
  2. On Error GoTo Err_UpdateWarrant_Click
  3.         Forms!Vehicleform!VehicleWarrantExp = DateAdd("m", [WarrantTerm], [NewWarrantDate])
  4.  
  5. Exit_UpdateWarrant_Click:
  6.     Exit Sub
  7.  
  8. Err_UpdateWarrant_Click:
  9.     MsgBox Err.Description
  10.     Resume Exit_UpdateWarrant_Click
  11.  
  12. End Sub
This has indeed updated the fields in Question from within the [Vehicleform] however when run from the within the [ClientForm] which is the Parent for the [VehicleForm] I get an error stating that [VehicleForm] "Can-not be found" :/

Yes I have tried
Expand|Select|Wrap|Line Numbers
  1. Forms!ClientForm!VehicleForm!VehicleWarrantExp = 
  2.  
as well as ;

Expand|Select|Wrap|Line Numbers
  1. Forms!ClientForm.VehicleForm!VehicleWarrantExp=
Nearly there
Jun 15 '10 #8

P: 11
Finally Resolved in the post below:

Subform
Jun 15 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.