Having calculating field in form to appear in table

[chigley3]

Hello,

I have created a table which I then created a form. I need the forms information to be stored in the table. Most fields work.

However, I had to create an expression in some of the fields on the form in order to do some maths. Eg (Labour cost * labour hours = Labour rate.)

Now because I put the expression in the controlsource, it wont update the information into the table because I guess its not linked to it anymore.

Please help me.

Thank you in advance,

C.

 

[PHV]

Why would you store derived/calculated values ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chigley3]

Because I need to visually see the information as it is important financial data.

I can send you the database if you want to look?

 

[PHV]

I need to visually see the information
So, do the math in the query.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[chigley3]

Ok, if I do the math in the Query where do I put the expression?

I am trying to put it in the 'criteria box' but its not working

=[SB Material Charge]+[SB Labour Charge]

 

[MajP]

If you do it in the query builder to build a calculated control
New Name: formula

TotalCharge:[SB Material Charge]+[SB Labour Charge]
LaborRate: [Labour cost] * [labour hours]

If you do it in Sql this will translate to something like

Select, Field1, Field2,... [SB Material Charge] + [SB Labour Charge] As TotalCharge, [Labour cost] * [labour hours]LaborRate,

 

[chigley3]

I FEEL SO STUPID, YOU HAVE TO ASSUME I HAVE NEVER USED ACCESS BEFORE.

Under the field SB total in the criteria box i put
SB total:[sb material]+[sb Labour charge]

Its not working, please do a step by step. I know it must irritate you experts but I thought it would be easier then this.

thank you so much

 

[randy700]

Under the field SB total in the criteria box i put
SB total:[sb material]+[sb Labour charge]

Move it to the Field box.


Randy

 

[MajP]

If you google "using calculated fields in a query" you will get a ton of step by step instructions with screen shots. Here is one:

http://articles.techrepublic.com.com/5100-10878_11-1051968.html

 

[chigley3]

ok i simplified the whole thing now

this is exactly what i write

in the total field i the criteria i put

total:[labour]+[material}

nothing happens when i type the numbers into the boxes

so i type in the total criteria box

[labour]+[material]

nothing happens

 

[MajP]

Did you read Randy's post or the link?

in the total field i the criteria i put

In the FIELD box not the CRITERIA box. Just to be clear in the FIELD box not the CRITERIA box, put the following
total:[labour]+[material]

 

[chigley3]

i got the formular working in the query, how does that then update in a linked table?

and if i create a field how does it store and if i enter it onto a form how does it update onto the original table. (form derived from the query)

 

[MajP]

It does not.
If I can calculate A+B today, then I can calculate it again tomorrow. I do not need to store it.

Except for the most trivial, all forms and reports should be based off of a query and not a table, and users should never enter data into a table.

 

[chigley3]

i can possibly keep going back to the same form and doing the formulae over and over again. I need the results of a calculation stored because i use those result in other areas. The point is to cut down on work not increase it.

if a + b = c then I want to be able to look at c when ever i want.

All I wanted was to create a form linked to a table so that some person can enter data into it and for it to be saved to be looked at whenever.

I know that matsh is better on a spreadsheet but its not efficient like access to see everything in one screen as a pose to scrolling across loads of tabs.

 

[MajP]

You are not getting it.

If I build a query that calculates a value I can use it anywhere. I write it once an use it everywhere, or at least in a lot of different places.
I can use that query as part of a recordsource for a form, report, or control. I can use a dlookup to pull that value from the query then use that that in a function or subroutine. So I am not sure why you think you can not use this in other places.

If your database is properly designed then you write the formula once. In the worse case maybe you have to write it again in another query.

I need the results of a calculation stored because i use those result in other areas. The point is to cut down on work not increase it

You may need to read up on relational database design, if you can not understand this concept.


The only times I store calculated values:
1) The calculation are so complex that they make running the query impossible or at least painfully slow. I have a couple of db running integral calculus or determining missing values in sequences.
2) I need a historical record so that the value is stored along with a date.

So here is the big drawback of storing a calculated value.
Say I have A and B as values and I am calculating in my query:
C = sqr(A^2 * b^2)
If I go back and change A or B from any form, query, or table then my db shows the new C. It will always be correct. If I store this value I have to change A or B only in the form that has code to store C. If I go to a query or table or other form without the code my C will be wrong and I will have no idea. If I was a customer relying on the integrity of the data, I would reject any database design that stores values because of these data integrity issues.

 

[chigley3]

I just worked it out myself,

If you put for example, after update and then go into Code.

you put labourcost.value = [labourrate]*[labourhours]

This then updates it on the form AND on the table.

No need for queries whatsoever.

 

[MajP]

Great, you figured out how to do it wrong. There are a lot of poor database design techniques that you can make work. Normally this will come back to bite you later. I think we made it clear why normally you do not save calculated values, and how to do it correctly.