Formulas in Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have this problem all mapped out in MS Excel, but I would like to convert
this issue into my Access database. I'd basically like to do two separate
things based off of some of my fields.

I have a start date (1/1/2004) and an end date (12/1/2004) and based off of
the week intervals, I am basically saying that a worker will work the normal
40 hours per week and we are alloting them an average of 5 hours of overtime,
to equal a total of 45.

Based on the following formula, I can derive at the maximum total of hours
between the start date and end that. The second notion is to take those
total hours and multiply that by the hourly rate of my employees.

First forumla in Excel to arrive at maximum total hours -
"=product(sum(enddate - startdate)/7*45)"
Second forumla in Excel to arrive at exceed cost amount -
"=product(sum(maxhours*hourlyrate))"

I would love to translate this same information into my MS Access database
so that the "Maximum Hours" and "Exceed Amount" fields are automatically
populated based on the information I place in the "start date," "end date,"
and "hourly rate" fields.

Thanks in advance for your assistance.

KAJ
 
Kevin

Why? As in "why do you feel it necessary to convert something that works in
Excel into Access?"

And a word of caution -- as a relational database, Access doesn't really
need to (and some would say "should not") store calculated values. In
Access, calculated values are generally determined in a query and produce
"on the fly".
 
Thanks for the advice.

With that said, can I create a mail merge or some other feature that will
allow me to automatically draw the Access information, calculate the formulas
described below in Excel and then have that information automatically updated
for each employee in a statement of work document in Word?

To be more clear, I will be using the mail merge option to draw much of the
information from the database (employee name, contractor agreement number,
etc.), so that information is automated in the statement of work Word doc.
From there, I also wanted to autmotically update for each employee the start
date, end date, maximum hours and exceed amount within the same document. I
was hoping to pull this information from Access (with the calculatins
automatically generated), but if you suggest that is a bad idea, how can I
have the Word doc draw the information from Access and still draw the
information with the calculations from Excel within the same doc? How would
all three be linked?

Thanks again for your advice!

KAJ
 
Kevin

You are asking for a fair amount of automation to build a system that uses
Access, Excel and Word. Unless there is a specific question, this is
"bigger than a breadbox".

Perhaps someone else reading this 'group has the time to devote to
designing/helping you design this level of automation...
 
Back
Top