Calculated fields

[flap14]

Hi
I have what is probably a stupid question but I've been struggling with this one for a while.
I have an ordering database which has an Orders table (containing Order ID, Date, Supplier etc) and an Order Line table within which I have a combo box for Product name, supplier, price, VAT rate, Line price etc.
At the moment, I have the order line table as a subform within the Order form (run from the Orders table).
The problem I'm having is the subtotal and total fields. At the moment these are in the Order Line table as I cannot figure out how to get these in the Orders table.
In summary, can I make a calculated field in one table that calculates values from another table (linked by Order ID)?
Thanks

 

[namliam]

Date is a reserved word, you are ill adviced to use it as a column name for sooner or later it will cause you problems. Much better to call it OrderDate or something.

As for your question, calcalated fields should NOT be stored in ANY table. Instead simply calculate them as you need them

 

[chellebell1689]

I have a db that has a calculated field (Age) for the members of my church. So since I can't store the data, but want it ready when I want to look at it, I created a form that will show me all the information I have for each member (one at a time). I also created a query that calculates the age for me. Then I added a field that is linked to that query and set it up so that when I open the detail form, it automatically updates that field for me.

This might be a route you want to take. Plus forms make it look nice and you don't have to go searching for a field when you already know the location on the form.

 

[namliam]

Chellebell, thanks for chiming in...

Your solution using a query is the right way, though a slightly better way, since the query is accessing your member table anyways... is to have it fetch the required fields of your form in one go instead of joining the query and table in your form

By not joining but only accessing the member table thru the query you should gain some performance.... if your table isnt too big it isnt much of an issue.... even if your table grows huge, the difference with just the one table will not be seriously noticable...
However if you have more of these kinds of solutions, it can really put a drag on your database.