Help with doing calculations in queries

[DB88]

Hello,



I've been using another database software for years but just changed jobs and I have use Access now. I was told that Access can't use data from related tables to create calculation fields. That was my bread and butter in my other program. Now I don't even know how to start designing my database.

I was advised to "Build a query. Or use domain aggregate function in textbox on form/report." Unfortunately, I don't see how either of those options will solve my problem and would really appreciate someone breaking it down for me.
For simplicity, say I have a table, Project that contains the basic assumptions of my project. For each project there are things that change year to year, so I have a table, YearData. There are 30 years for each project. Now to calculate cost per year, I need to multiply the values in one year's record with the overal project assumptions. The end result would be a new field in YearData that is (Projects::assumption1)*(YearData::field1). How would I perform and store this calculation? Once performed would information update automatically if the dependent variables changed?

Thanks

[June7]

A basic principle of relational database is to save raw data and do calcs in queries. Saving calculated data, especially aggregate data, is usually not advisable.

If calculated value must be saved to a table, that requires code.

Build query that appropriately joins tables. Do calculations in the query or in textboxes on form or report. Access reports have features to allow summary calcs in headers and footers and still display detail records.

You should probably get a tutorial book on Access and become familiar with its features.

[DB88]

My other software saved the calculation structure in a field and would produce the result as needed and a query was just to find records. I've been developing in FileMaker for 5+ years and now being forced into a different software I realize how unique it was.

I agree a tutorial book would be helpful. Could you recommend a good one? None that I have seen show me how to create calculations in a query, just how to find records.

[June7]

Any tutorial book should have info on building expressions. I have 3 very old books and all do. Look for Expression Builder or Calculated Fields (or Fields Adding Calculated) in the index.

I never use the Expression Builder. I just type the expressions I want into the Field row of query designer. For example, say there are two fields named Quantity and Price, the expression would simply be:

[Quantity] * [Price]

Once you learn the operators and functions, typing is much faster than the Expression Builder.

Search Access Help for references on building expressions.