normalization?! I just want to have an order total in parent form...

[ethan1701]

Okay, here's my situation:
I have a orders_total table and an orders_detail table. they are joined by order_id, which is a unique key in orders_total. I built a form with a subform built on these two tables. In orders_total, I have a column named orders_income. I'd like to make a textbox in the form which is bound to this column, and would be updated with every change to the total value of the products sold, based on the sub-form's products_price*products_quantity.
Within the subform, I managed to create an unbound textbox that displayes the total income per product, but I can't figure out how to bind a calculated field to a table.

Now I've read around here about normalization. WTF?! I think having the total income from an order stored in ad order_total table is elementary in database structures. It would be totally absurd to have to query two tables for this information...

Can anyone guide me as to how I can bind the calculated field? take into account I've never used access before (I'm well versed in databases and VBA, just never used access), so please be specific as to where I find any of the things you refer to.

I really appreciate your help,

-Ethan

 

[sillysod]

well as far as normalization goes you should never have one field thats value is dependant on another, ie calculated values.

The reason bing it WILL lead to anomilies at some point, for whatever reason the routine below might not get fired or maybe some makes an ammendment to the tables and doesnt update the other.

If you do have a total field in parent table is to have it as a memorandum field only, ie do not perform any critical calculations on it, such as a months "orders recieved" report etc.

but if you want to do it, put this in after update event of subform.

dim TotalIncome as double
dim Quantity as integer
dim PricePer as Double

Quantity = txtWhatever.value
PricePer = txtOtherWhatever.value

TotalIncome = PricePer * Quantity

txtYourTotalTextBox.value = TotalIncome


Then bind txtYourTotalTextBox to the order total field of your parent table