Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Question Unanswered: How can I store a calculation in a table field?

    I know generally you're not supposed to do this but ......

    (using Access 2003)

    I want to store the cost of a job so that reports can be generated with total costs for a client.

    The two tables needed for calculation are;

    tblStaff
    staff_id (PK)
    hourly_rate
    tblJob
    job_id (PK)
    staff_id (FK)
    time
    cost <-- this is where I want to store the cost!

    The cost of the job needs to be calculated from

    tblStaff : hourly_rate
    *
    tblJob : time

    and stored into........ tblJob : cost

    I need the calculation to be stored at the time the record is made as I do not want future changes in staff hourly pay rates to alter past records.

    Does anybody know any way to do this?

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Can I suggest an alternate? Add a field into tblJob called staff_hourly_rate, and store the rate there. That way, raises won't affect it. The total cost can then be calculated in a query directly from tblJobs.

    Sam

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    thanks sam, sounds like a plan .... could this be filled in automatically by copying the current rate in the staff table?
    i really want to keep the table as quick and easy as poss to fill in! .... and stop the wrong data being entered! also, only admin should have access to the hourly rates but any staff should be able to put a job into the system.

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Yes to your question.

    If you are really concerned with security, and don't want users to be able to see current wages as entered into the Job table, I suggest that the data entry be done via a form with a combobox that can enter the person's number and current wage into the table. The combobox's rowsource would be the entire contents of tblStaff. You would set the column width of the wage column to 0 in the property sheet, which hides it. Even though it's hidden from view of the user, though, you can still store it in the table.

    BTW, I'm curious why your Staff table doesn't contain a field for the name; or are you just posting a few fields here on the forum? The name is really the data you want the user to see (in the combo box), not his id number and certainly not his current wages. With this in mind, you would set the column width for the id number to 0 as well, and sort the rowsource on the name.

    See the help file concerning using comboboxes for more details on how to manipulate the data contained in them.

    Sam

  5. #5
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The other method of handling this sort of problem, frequently associated with tax rates that change regularly, is to have a separate table for rates along with the Effective Date for a given rate. Then anytime the rate was needed, you'd retrieve it, comparing the date on the record with the dates in the rates table.

    Some 'purists' prefer this approach, but having to retrieve this value, each and every time you need a rate, either for a current record or for a historic record, has always seemed to me to be an incredible amount of extra work, and so I use Sam's approach!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    Join Date
    Mar 2012
    Posts
    3

    Talking

    thank you again sam, I shall look more into the use of combos (I'd never thought that one could be used to store more than one item!), will check out the combo help but don't worry ... you'll hear from me again when (not if) I get stuck lol.
    and, yes ... the tables do contian more fields than I have shown, was just trying to keep things simple by showing the fields involved in this particular problem

    thanks missinglinq, the extra table idea does sound accurate, will keep that in mind for future problems, but I agree .... too much work for the current job lol

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •