Editing a calculated field

[matthewnorton]

Hey all,

I have come across a problem with my database that I would appreciate some help with.

I have added to an existing table by creating a query with calculated fields (calculated from fields in the original table).
However, a few values in the calculated fields result in "error" or are null, this is due to the values in the original fields.

I have been given default values to substitute in for these error values, but I have found that I am unable to "edit" cells in a calculated field. (i.e. just trying to type a value in, replacing the "error").

Does any body know how I could go about putting these values in?

Or is it just a case that as they are known (not calculated), then they have to be included in the original table?
If so, how would I go about getting the calculated and non-calculated values in the same field?

Any help will be appreciated,

Thanks all,

Matt.

 

[Len Boorman]

Basically if it is a calculated value then do not store it. Calculate it when needed. Ensure that there are default values for fields that will be used in the calculation.

Len

 

[matthewnorton]

Thanks for the quick reply Len,

The calculated values are only in the query. The problem that I am trying to express is that the calculated field (e.g. Total) doesn't give a useable value for every record.

i.e. the calculated field (Total) applies the same formula to every record and returns a value. Now, my problem is that some of these values are "Errors" and I would like to edit them to a default value that has been pre-determined.

I am finding that this is not possible and I am wondering whether there is a way to achieve this.

Obviously I can't put the calculated values in a regular table, as they are calculated. But at present I am unable to edit the calculated field to contain the default values.

I hope I have made my problem clearer this time.

Many thanks,

Matt.

 

[Len Boorman]

Maybe a couple of options
1) Exclude those items where one part of the calculation doesn't contain a valid figure bu putting a criteria in the query such as >0.

2) Use a maketable query to temporarily store the values, Use an update query to substitute in default values for missing items and then run your final query. Each time you run the sequence you will need to delete the temp table.

Could you not have a default value of zero in the main table. then anything that calculates to zero is a questionable result.

len

 

[matthewnorton]

Len,

Thanks for the suggestions.
There is no option for setting a default value of 0, as we have calculated the actual results experimentally for the "errors" (i.e. it could be 50 or 100).
The problem arises when trying to put this known value into the calculated field to replace the "error" text.
It is only that the values in the calculated field need to be used to do further calculations - at present this is not possible as errors obviously generate errors.
I guess what I am really asking (in an ideal world!) - is there an option in Access that will allow a user to edit the values from a calculated field?
i.e. breaking the integrity of the calculation - just for a couple of records.

Sorry if this is sounding trivial - but I need to find a solution.

Thanks once again for your time,

Matt.

 

[Len Boorman]

To my knowledge there is no way to edit the results of a calculated field because it is exactly that:. A Calculated field. Best optionfrom now is to use the query as a make table query and edit within this temporary table

Len

 

[matthewnorton]

Thanks Len,

I will certainly give this a try.

Thanks again for all your help!

Matt.

 

[neileg]

Erm, two points.

First, you can use the IsError() function to see if your calculation returns an error. Something like:
MyCalculatedField: Iif(IsError(insert your formula), 0, insert your formula)
Here, if the value was an error, the Iif() returns a zero instead. I use this technique a lot in Excel.

Secondly, if you use the temporary table method, you don't want to be using a make table query. Set up your temporary table, and then append the records to it. When you are finished, delete the records but keep the table for the next time.

 

[Len Boorman]

Hi Neil

Thought there must be an alternative but didn't know it myself

Len

 

[matthewnorton]

Ok, thanks for all the suggestions so far guys, but here is where I sound like a complete beginner...

The idea of the Temporary table sounds good to me, I am just a little unsure of how to implement it's use.

Say for example my query is:

EmpID HourlyWage HoursWorked Salary
1 4 28 112
2 5 - error
3 4 0 0
where "Salary" is a calculated field ([HourlyWage]*[HoursWorked])

but, we know that EmpID 2 and 3, will get a basic pays of 50 and 45 a week, respectively, even if they don't work. So no matter whether I put a "0" or leave it "null", the result given in the calculated field (Salary) will have to be edited.

In reality, only 1 out of every 20 records needs editing.

Would my Temporary table look like this?...

EmpID HourlyWage HoursWorked Salary
1 4 28 112
2 5 - 50
3 4 0 45

or do I only need the "EmpID" and "Salary" fields?

Then, the last question, is, how do I implement these values into the original query?
Do I remove the records for Emp 2 and 3 from the Query and then only use the records from the Temporary table?

Any guidance will be appreciated, as I have said, I am sorry if I seem ignorant to the basics.

Thankyou so much for the help,

Matt.

 

[Len Boorman]

so you have some fixed and some variable salary situations. So those with a fixed salary also have fixed hours ? Yes ?. and fixed rate ?Yes?. If so then can these not be put in as values which you do not update. Put a simple update indicator field (text Y or N) if N then do not select them for data input. Your query with the calculation then works and you do not have to tweak it in any way or use a temporary table,

A temporary table is the same as any other except that you use it to temporarily store information while you fiddle with it.

len