MySQL Forums
Forum List  »  Microsoft Access

normalization & calculated fields
Posted by: Daniel Hofer
Date: April 25, 2013 02:27PM

I ported my large end user developed Paradox database of 18 years to an Access\MySQL database this past year. In preparation for retirement next year I'm now working with IT to normalize part of the database to remove calculated fields as they work on porting some of the Access forms and reports to php web forms.

Relevent Part of Model:
`orders` 1 to many `jobs` 1 to many `lineitems` 1 to 1 `biditems`.

Calculated Fields to remove:
`lineitems`.`extended total` = `lineitems`.`units` * `biditems`.`price`.
`jobs`.`jobvalue` = the sum of related `lineitems`.`extended total` records
`orders`.`ordervalue` = the sum of related `jobs`.`jobvalue` records.

Everything is (re)calculated with a stored procedure when lineitems are added or updated.

If I remove the calculated fields in `lineitems`, `jobs` and `orders` I know how to use queries to calculated these values on the fly for reports.

What do I do to show these calculated control values in the form? The Orders form has a subform showing the jobs rows for an order and the jobs subform has a subsubform showing the lineitems rows for each job. I can handle the lineitems subform but not the jobs subform `jobvalue or orders form `ordervalue` fields. I can't base the forms on aggregation queries because of the need to update fields.

For the `jobs` subform `jobvalue` field I tried a row source expression:

=Sum(
IIf([jobno]=[Forms]![frmJobs]![frmLineitems].[Form]![jobno],
[Forms]![frmJobs]![frmLineitems].[Form]![units]
*
[Forms]![frmJobs]![frmLineitems].[Form]![price],
0)
)

This gives an error. Even if it works summing the above expression in an equivalent expression to calculate the ordervalue as sum of jobvalues seems very messy. It's even more complicated since there is an second parallel field earnedvalue at each level which is value * percentcomplete.

I'm open to ideas.

Options: ReplyQuote


Subject
Views
Written By
Posted
normalization & calculated fields
3038
April 25, 2013 02:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.