Register calculated field in table from Form (1 Viewer)

Hi all,

Very basic question here.

I need both to have a calculated field in a form and register the result in its table.

The calculation function needs to be placed in the Control Source, right? How can I select then in which field the result must be registered?

Should I just do the same calculation in the main table for that field?

Thanks in advance

setis said:

..How can I select then in which field the result must be registered?

Click to expand...

You can't. Calculated value shouldn't be stored but calculated each time you need it.

Not really sure what you're asking... typically you don't do a calculation in a table (some later versions of Access allow it, but it usually shouldn't be done: it's in place to help support one of their attempts at web database and definitely falls into the "just because you can doesn't mean you should" category).

That said, usually you'd do a calculated field in a query, then bind the form to the query (in which case you'd have access to that calculated field just like any other field in the query).

That's generally preferred, but sometimes not feasible (calculating a sum of records for a subform, for example), in which case you would just use the control itself and enter an expression.

I think a query with a calculated field is what you're looking for though.

jleach said:

Not really sure what you're asking... typically you don't do a calculation in a table (some later versions of Access allow it, but it usually shouldn't be done: it's in place to help support one of their attempts at web database and definitely falls into the "just because you can doesn't mean you should" category).

That said, usually you'd do a calculated field in a query, then bind the form to the query (in which case you'd have access to that calculated field just like any other field in the query).

That's generally preferred, but sometimes not feasible (calculating a sum of records for a subform, for example), in which case you would just use the control itself and enter an expression.

I think a query with a calculated field is what you're looking for though.

Click to expand...

Thanks a lot. I will do the calculations in a query instead of doing them in the table. It makes much more sense.

When you say "you'd do a calculated field in a query, then bind the form to the query", if I do this, will the form register further inputs in the actual table where the query is looking into?

Some queries are Updatable (meaning they can more or less act just like a table: the values can be updated and the underlying table(s) are likewise updated accordingly). Some are not. It depends on how the query is written... certain operations, such as grouping and displaying counts, would make the query non-updatable as it has know way to know which actual record to resolve to on the table level.

For simple queries, they're usually updateable. I would imagine you'll have what's basically a "SELECT ThisField, ThatField, 2*2 AS ThisCalculatedField FROM MyTable;" sort of query, in which case all fields (except the calculated one, of course) should be editable, and in which case the query can then be used as a form recordset as per usual.

JHB said:

You can't. Calculated value shouldn't be stored but calculated each time you need it.

Click to expand...

JHB is correct for almost all situations.

The only reason you would not recalculate a value is if the records is dependent on the value at a specific time and that value is subject to change. Example would be the sales price for a product if the system is not set to track price by date.

Likewise if there is a regulatory reason, such as a requirement to save total sales price (including taxes and fees).

Unless you have a very good reason to save a field you can calculate, don't.

For myself I have seen some situations where you do save calculated values, and these were saved to accumulator records. This is because it took about an hour to generate these results and these were totals used in monthly, quarterly, and annual fiscal reporting. As it made no sense (business wise) to tell the boss "Sorry, this won't be available until tomorrow afternoon", we saved the calculated values.

Mark_ said:

accumulator records

Click to expand...

Never knew these things had a name, but I like it.

Been a while since I've worked with one.
Last time was for a system that tracked activity on slot machines. Had totals for coin in, coin out, plays, payoffs (count / amount / max), that kind of stuff. As you could have thousands of records for a single machine per day, and thousands of machines on a floor, we had daily and monthly accumulators by machine.

Doing a report on 1000 machines when there are between 50k and 100k records per machine does take a long time to run. Same report running on 1000 records becomes something you can update on a desktop dashboard.

totalky dusagree with you all. and somebody is not telling the truth. there is no count or sum or max in calculated field. so nobody has really work with them. naking a false witness.

arnelgp said:

totalky dusagree with you all. and somebody is not telling the truth. there is no count or sum or max in calculated field. so nobody has really work with them. naking a false witness.

Click to expand...

So you can't think of a good reason, so you think somebody is not telling the truth?

So how would you return the activity of thousands of slot machines in real time if you don't have a break point with known values? Please make sure the results are returned within 5 seconds when you can't control the hardware or machine load.

I don't know if this has been asked before but why shouldn't you use a calculated field in a table? I see that the newer Ms Access allows it but what are the drawbacks?

For the most part, the reason you do not save calculated fields is because you can (normally) easily generate the value from going through existing records.

If you have, say, 100 sales records for a given customer and 20 payment records, going through the data base and selecting just those 100 sales to total "Amount owed" is trivial. Same with "Amount paid". You can then calculate the "balance due" by subtracting "Amount paid" from "Amount Owed".

When it does start to make sense to save a value is when the amount of data or the amount of processing becomes excessive. Say rather than 100 sales and 20 payments you were dealing with several hundred thousand sales, tens of thousands of payments, and you were trying to work through on a per sale basis when the payment was made to return 30/60/90 values. For most businesses this is generated as a report that is references. For some, they would instead update a dedicated record per customer with this type of data so current values are shown to sales people who need to make discount decision based on how timely payments are made.

In the real world cases I've seen them used it was based off of slot machine tracking. Each machine can generate tens of thousands of records per day. The "Play" data was accumulated so that daily records could be archived. We were also building hourly accumulators (plan to update to every 15 minutes before the project ended) so that for a given location on the floor, performance by machine type by day/time could be measured against a host of other odd data for marketing and machine placement decisions. Rather difficult to answer "Which types of machines have the most play when customers are queued for a R&B concert" if you can't readily give results by time period / placement. As some of this was to be for "in a three year time" to see if it made sense to rearrange machines prior to events, you can see why getting the results timely would be very relevant.

You don't save calculated fields in tables because each column in a table should depend ONLY on the primary key. A calculated value always requires at least two operands and therefore can't possibly depend on one value. The danger is that when you save a calculated value, one of the operands can change and that makes the saved result invalid. Now, the promise is - I'll always make sure that if any procedure or query modifies one of the operands, I will force the saved value to recalculate. The reality is, people are sloppy. They forget the requirement or someone new takes over the app and they don't even know the requirement. One example, I've seen several times is for applications that want to know how old a person is. They run an update query or do it in a form and save the calculated result. Think about this. Unless you recalculate age just after midnight every night, at least some records will ALWAYS have the wrong age. So just don't do it.

Mark was talking about a warehouse application. Warehouse applications are used for reporting and they frequently summarize data to make the tables smaller and help the users with common tasks so they don't have to be done in every query. The difference is that Warehouses are not transaction databases. No one opens a form and updates just a single record. The entire set of data is replaced, usually monthly but occasionally weekly depending on the application. Or perhaps, every week a new batch of summarized data is added.

You haven't said what calculated value you want to store. If the calculation uses ONLY columns from a single record, you would never store the calculated value. You would always calculate it in the query. Other situations are case by case. You just have to keep in mind that if you insist on breaking the rules, you need to have a very good reason and you need to be prepared for your data to be invalid. If having your query produce invalid results could cause your client or employer to make a financial mistake, don't even think of storing the value.

So tell us what you want to store and why you think you can't calculate it on the fly and we'll offer specific opinions on how dangerous we think storing that particular piece of data is.

I was thinking of a payroll table. Using:-

Gross Pay = [Hours Worked]x[Hourly Rate]
Net Pay = [Gross Pay]-[Deductions]

[Gross] and [Net Pay] are the stored calculated fields, At Table Level.

[Hours Worked] and [Hourly Rate] are variable and entered through a form and stored in the table.

raziel3 said:

I was thinking of a payroll table. Using:-

Gross Pay = [Hours Worked]x[Hourly Rate]
Net Pay = [Gross Pay]-[Deductions]

[Gross] and [Net Pay] are the stored calculated fields, At Table Level.

[Hours Worked] and [Hourly Rate] are variable and entered through a form and stored in the table.

Click to expand...

Is there a regulatory requirement to save this? If not, there is no real advantage to saving it in the record. As Pat has outlined you can very easily calculate these values. Trying to save them and update them if underlying data changes is more work that it is worth.

Special note, for deductions I am under the impression you are reading values from a linking record? You would normally have standard deductions for an employee but created a "Link" record between each deduction and the record of pay.

There is no regulatory requirement but I was taking in to account if the employee's [Hourly Rate] changed in the future. So essentially what you are saying it is better to save values to a table, not the formulas and to do all calculations in the form.

raziel3 said:

There is no regulatory requirement but I was taking in to account if the employee's [Hourly Rate] changed in the future. So essentially what you are saying it is better to save values to a table, not the formulas and to do all calculations in the form.

Click to expand...

Personally I tend to prefer recording a history of changes and effective dates rather than storing the effective value at the time of calculation.

So we'd have a table of HourlyRates per employee with an EffectiveDate, and a query (or view/sproc if working with MSSQL) that returns all of the effective values for a given date.

Either that or some sort of temporal database (because really, shouldn't everything be stored in an "as-of" context?): http://www.timeconsult.com/TemporalData/TemporalDB.html

Storing the hourly rate at the time of payment is not a problem and doesn't violate normal forms. Storing it is much easier than figuring out what the rate was at any point in time. Storing the hours worked in the same record is fine since that is the data you are collecting but storing a third field which is Hours * rate is unnecessary and leaves you open to problems if someone were to ever change the Hours or the rate in an existing record. I'm sure you would say "this NEVER happens". But the reality is that it does, even if it is a simple correction because someone noticed that the hours were wrong.

If you need to report on rate changes over time, by all means also keep a history. The two are separate actions.
Using a history is easiest if each record has both a start and an end date. The current record simply has an end date far into the future. However the code to manage this is much more difficult than if the history table contained only an effective date making the rate effective until the next record is added.

jleach I took your advice with regards to an effective date. I am wondering though which would be the most effective,
1. Using a Dlookup to fill the rate on a form
2. I saw MarkK suggested VBA

Const SQL_SELECT As String = _
"SELECT TOP 1 Price " & _
"FROM PriceHistory " & _
"WHERE ProductID = p0 " & _
"AND StartDate <= p1 " & _
"ORDER BY StartDate DESC;"

With CurrentDb.CreateQueryDef("", SQL_SELECT)
.Parameters("p0") = Me.ItemName.Column(1)
.Parameters("p1") = Forms!Sales.SaleDate
Me.PriceH = .OpenRecordset!Price
.Close
End With

Click to expand...

based on a similar issue another user had
3. Or an SQL

I have a Payroll entry Form

[cboemp] references the Employee Name
[Periodend] is the end of the pay period


I'm using the following expression to fill a Rate textbox on the Payroll Form

DLookUp("[Monthly Rate]","EmpRates","[EMPID]= '" & [cboemp] & "' And DMax("[Effective Date]","EmpRates","[Effective Date]<=[Periodend]"))

but the rate is not changing when I enter a future date in [Period End]

The EmpRates Table has

EMPID Monthly Rate Effective Date
J. Dawn $6200 9/1/2017
J. Dawn $7500 4/1/2018

Your query will return multiple records. As I said, a history table is difficult to use if it doesn't have an end date.