Calculated field

[Alan T]

I have a subform.
The link table of this subform is tblCharges, fields are:
ID
EmployeeDailyRate
EmployeeDaysWorked
EmployeePay

I need to calculate the EmployeePay and fill in the value after I entered
the EmployeeDailyRate and EmployeeDaysWorked.
EmployeePay = EmployeeDailyRate * EmployeeDaysWorked

 

[Guest]

Am I guessing "EmployeePay" is a bound field in the table? This is really a
no, no!

Calculated information shouldn't really be "hardwired". Do it in a query
instead.

EmployeePay: [EmployeeDailyRate] * [EmployeeDaysWorked]





--
http://www.patriotsquestion911.com
http://www.911truth.org
http://www.wtc7.net
http://www.st911.org
http://www.journalof911studies.com
http://www.pilotsfor911truth.org
http://ae911truth.org

 

[John W. Vinson]

I have a subform.
The link table of this subform is tblCharges, fields are:
ID
EmployeeDailyRate
EmployeeDaysWorked
EmployeePay

I need to calculate the EmployeePay and fill in the value after I entered
the EmployeeDailyRate and EmployeeDaysWorked.
EmployeePay = EmployeeDailyRate * EmployeeDaysWorked

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[Alan T]

If I don't store the calculated field in the table, how do I represent in
the datasheet view o the subform?
If as you suggested, in the subform, I need to display 3 columns,
EmployeeDailyRate <-- field from table, user to enter
EmployeeDaysWorked <-- field from table, user to enter
EmployeePay <-- auto-calculate

How do I link this subform to record set? And how to trigger the
auto-calculation?

 

[Douglas J. Steele]

You create a query that does the calculation, and use the query as the
recordsource for the form, not the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

 

[Alan T]

Is that something like:

SELECT DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee

Will it create a new record when the user press the * button on the record
selector?

 

[Douglas J. Steele]

Yes to both questions.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is that something like:

SELECT DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee

Will it create a new record when the user press the * button on the record
selector?

You create a query that does the calculation, and use the query as the
recordsource for the form, not the table.

 

[Alan T]

Hi,

I still got the problem of auto-calculation:
I use the query
SELECT DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee

When I run the main form and then enter the details in the subform:
DailyRate and DaysWorked in the datasheet first record, the third column -
Pay did not get display the value.
Even I exit the application, the field Pay in the table had no value inside.

Any idea?

Yes to both questions.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is that something like:

SELECT DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee

Will it create a new record when the user press the * button on the
record selector?

You create a query that does the calculation, and use the query as the
recordsource for the form, not the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


If I don't store the calculated field in the table, how do I represent
in the datasheet view o the subform?
If as you suggested, in the subform, I need to display 3 columns,
EmployeeDailyRate <-- field from table, user to enter
EmployeeDaysWorked <-- field from table, user to enter
EmployeePay <-- auto-calculate

How do I link this subform to record set? And how to trigger the
auto-calculation?




On Fri, 18 May 2007 16:26:08 +1000, "Alan T"
<[email protected]>
wrote:

I have a subform.
The link table of this subform is tblCharges, fields are:
ID
EmployeeDailyRate
EmployeeDaysWorked
EmployeePay

I need to calculate the EmployeePay and fill in the value after I
entered
the EmployeeDailyRate and EmployeeDaysWorked.
EmployeePay = EmployeeDailyRate * EmployeeDaysWorked

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi,

I still got the problem of auto-calculation:
I use the query
SELECT DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee

When I run the main form and then enter the details in the subform:
DailyRate and DaysWorked in the datasheet first record, the third column -
Pay did not get display the value.

Is the Control Source of the third column Pay? It should display if so...

Even I exit the application, the field Pay in the table had no value inside.

The field Pay *SHOULD NOT EXIST* in the table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[Alan T]

Hi,

Now my subform can show the calculated fields after tried several times.
Thank you.

Here I have another question regarding my same subform:
I have another field called LineNumber which is the incremental number of
the employee pay starting from 1, to indicate the number of pay records in
the subform.

Is it possible to show in the subform?
ie
SELECT LineNumber, DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee
To make the LineNumber start from 1 then increment by 1 then.

eg.
LineNumber DaillyRate, DaysWorked, Pay
1 $200 2 $400
2 $250 3 $750
3 $200 1 $200

Hi,

I still got the problem of auto-calculation:
I use the query
SELECT DailyRate, DaysWorked, [DailyRate] * [DaysWorked] as Pay
FROM Employee

When I run the main form and then enter the details in the subform:
DailyRate and DaysWorked in the datasheet first record, the third column -
Pay did not get display the value.

Is the Control Source of the third column Pay? It should display if so...

Even I exit the application, the field Pay in the table had no value
inside.

The field Pay *SHOULD NOT EXIST* in the table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]