Saving data into a table field by a query calculation

[Gilberto]

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

[mlcampeau]

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?

[Gilberto]

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

[mlcampeau]

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.

[Gilberto]

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

[Weise]

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 [VehicleWarrantE xp] in the [VehicleDetailsT able] (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

[Weise]

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 [VehicleWarrantE xp] in the [VehicleDetailsT able] (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

Appended

Bob Olston from allexperts.com replied.

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

YOu could also use

me.parent.contr olname
---------------------------------------------------

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 [NewWarrantExpir y] Date to be saved back to the VehicleForm/Table and update the [VehicleWarrantE xp] 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])

[Weise]

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 >!<controlnam e>

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

Forms!Vehiclefo rm!newwarrantex piry = me.newwarrantda te

Some free Access training is listed here:

http://webpages.charter.net/bobalston/AccessDB.htm#Free_Access_Online_Tutorials

and good bebinner books

http://databases.about.com/od/tutorials/tp/accessbooks.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 ButtonPropertie s.

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

in the subsequent vba dialog I entered the following code.

Private Sub UpdateWarrant_Click()
On Error GoTo Err_UpdateWarrant_Click
        Forms!Vehicleform!VehicleWarrantExp = DateAdd("m", [WarrantTerm], [NewWarrantDate])

Exit_UpdateWarrant_Click:
    Exit Sub

Err_UpdateWarrant_Click:
    MsgBox Err.Description
    Resume Exit_UpdateWarrant_Click
    
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

Forms!ClientForm!VehicleForm!VehicleWarrantExp =

as well as ;

Forms!ClientForm.VehicleForm!VehicleWarrantExp=

Nearly there

[Weise]

Finally Resolved in the post below:

Subform