Register calculated field in table from Form

[setis]

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

 

[JHB]

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

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

 

[jleach]

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.

 

[setis]

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.

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?

 

[jleach]

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.

 

[Mark_]

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

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.

 

[jleach]

accumulator records

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

 

[Mark_]

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.

 

[arnelgp]

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.

 

[Mark_]

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.

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.

 

[raziel3]

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?

 

[Mark_]

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.

 

[raziel3]

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.

 

[Mark_]

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.

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.

 

[raziel3]

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.

 

[jleach]

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.

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

 

[raziel3]

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

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

 

[arnelgp]

You cn use Public function ti get the current rate feim your table:

Public function effectiveRate(empID as string,periodEnd as date) as double
Dim rs as dao.recordset
On error resume next
Set rs=currentdb.openrecordset( _
"Select top 1 [monthly rate] from EmpRates where empID='" & empID & " and [effective date]<=#" & format(periodEnd, "mm/dd/yyyy") & "# order by [effective date] desc")
If not (rs.bof and rs.eof) then
effectiveRate=rs(0)
End if
Rs.close
Set rs=nothing
End function


'Xxx
Call the function from current event of form and afterupdate event of empid.

 

[raziel3]

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

I came up with this:

DLookUp("[Monthly Rate]","EmpRates","[EMPID] = '" & [cboemp] & "' AND [Effective Date]=#" & DMax("[Effective Date]","EmpRates","[Effective Date]<=#" & [periodend] & "#") & "#")

Is this future proof?

arnelgp I appreciate your suggestion but I can't understand it. In particular 'Select Top 1' and shouldn't the Function update the text box that has the Rate for example I have a text box named [txtmthrate] that have the Dlookup shown above.
In your function shouldn't it have something like
Me.txtmthrate = something??

 

[raziel3]

Assuming there are no syntax errors, that should work. As I said, I wouldn't do it this way. If I were going to keep history, I would keep an end date. That adds complications to the form that manages the rates but simplifies other processes. You only add a record once. Depending on the app, you may use it dozens of times.

I am trying to understand the 'End Date' concept. Wouldn't the 'New' [Effective Date] be the ending for the last Rate? For Example

EMPID_____________RATE______________EFFECTIVE DATE
J.DAWN___________$6200__________________9/1/17
J.DAWN___________$7200__________________4/1/18

In the table above 3/31/18 would be the 'End Date' that the Rate of $6200 is used, right?

This is how I've setup my Employee Rates Table.