How do I set a macro to input data calculations into a table?

[Vormov]

I have a database which contains financial data. I need to write a macro to
input calculations from one table into another. I have already set up the
relationships, but I need to apply a function to data in table A and have it
be saved in table B. (ie. gross profit calculations are going into table B
by project #). Any help would be greatly appreciated.

 

[Nikos Yannacopoulos]

Vormov,

This is a typical error; the answer is you don't do it at all! The
general rule is, if you can calculate it then don't store it, just
calculate it on the fly (in a query, form or report) when you need the
calculated figure. Otherwise, you'll be storing redundant data, plus
running the risk of failing to update the storwed calculated values if a
"base" figure changes, thus ending up with inconsistent data.

HTH,
Nikos

 

[Vormov]

I need the data stored so it can be used in other reports. If it is just
calculated locally, how can I make it present on other reports and forms?
Also, how can I get average figures for the calculations? (ie. average gross
profit per job by salesperson?) Thank you for your response.

 

[Nikos Yannacopoulos]

Vormov,

"Locally" is wherever you need it! That is to say, if you need it in
areport, calculate it in the report, or the query that "feeds" it.
Remember, you don't have to show all fields/sections in a report, so,
for instance, if you need a dollar average per case, you could have all
the line items at detail level in your report, sum the cases and values
from the line items in the footer section, and divide them to get your
average; you don't have to show the detail section (Visible = No) if you
don;t want it, nor do you have to show the case and value totals. This
way you only show the average, while the figure is calculated locally
rather than stored. Another approach to the same end would be to use a
totals query to aggregate cases and values, feed that to the report, and
just do the division in the report.

HTH,
Nikos