Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2009
    Posts
    22

    Unanswered: Simple question on calculating.

    First off, this forum has helped me out amazing this far. coming from not knowing access, to being able to get myself into some trouble

    I was asked by my boss to create an error tracking system for employees. My current problem is this.

    I am not the user who fills out the report and I noticed that the Total Cost field in Form view was not being calculated correctly. So I decided to make it calculate on its own. (=([PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS]) <--- these are fields the user fills out in the form

    But since I did that the Main data table no longer records what the Total Cost from the form view is.

    I need to be able to record this because when I pull a report of all the errors of a given month. I need to know the total cost of each error form as well as the grand total of all the errors.

    Any help would be much appreciated.

    Cheers.

    Rob.

  2. #2
    Join Date
    Mar 2007
    Posts
    277
    Do your calculation in the underlying query rather than in the ControlSource of a control on your form. Use the same query for your report.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  3. #3
    Join Date
    Aug 2009
    Posts
    22
    Is it possible if you could give me an example on how to do that? I just don't know where the query to add a "new" category for total cost.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select [PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS]) as TotalCosts from MyTable order by some,column,or,other

    I'd be very wary of hardcoding a vlaue such as 65 (presumably thats you cost/charegout rate) so I'd expect to pull that cost from another table
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2009
    Posts
    22
    Quote Originally Posted by healdem
    select [PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS]) as TotalCosts from MyTable order by some,column,or,other

    I'd be very wary of hardcoding a vlaue such as 65 (presumably thats you cost/charegout rate) so I'd expect to pull that cost from another table

    I'm sorry I don't quite understand.

    I guess my main problem is. I don't know how to make my table "add" field values from a form together. so in the above problem. I was able to add and display form data. but not take that calcualted data and place it in a table.

    As for the 65 value. I fully agree and will change that.

  6. #6
    Join Date
    Mar 2007
    Posts
    277
    You should not store calculated values in tables. Just calculate them again in a query when you need them.
    RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP, ac07, ac10 - WinXP Pro, Win7 Pro
    Please reply to this forum so all may learn.

  7. #7
    Join Date
    Aug 2009
    Posts
    22
    I know. But I have no clue on how to calculate that in a query this is my problem.

    Where can I find the option to do this?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    copy the SQL from post 4 into a SQL view in the query designer

    or

    open the query browser
    add the table(s) required for your calcualtion
    add a column set its source to
    TotalCosts:[PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS])
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Aug 2009
    Posts
    22
    Ok so when I copy post 4 "([PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS])" into the SQL I get a syntax FORM error. I even tried to add TOTALCOSTS:= infront of it.

    As for query "browser" In new to access so terminology comes as I encounter it. I have access 2007 so basically I go

    Click on my query to edit.
    Click desgin view.
    Insert in a column.
    (field name is blank)
    Add in exactly "TotalCosts:[PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS])" in the Source database.

    This did not work. It actually corrupted the query giving me an error about my network!?!? Naturally I had a backup file of course.

    Where am I going wrong?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so either copy the full text
    select [PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS]) as TotalCosts
    or
    add the text from post 8 to a column definition in a query desinger

    as to how you do that in A2007 I haven't a scooby... I odn't use A2007
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Aug 2009
    Posts
    22
    Quote Originally Posted by healdem
    so either copy the full text
    select [PRODUCTION HOURS]*65)+([ENGINEERING HOURS]*65)+[MATERIAL COSTS]+[FREIGHT COSTS]+[CUSTOMER COSTS]+[OTHER COSTS]) as TotalCosts
    or
    add the text from post 8 to a column definition in a query desinger

    as to how you do that in A2007 I haven't a scooby... I odn't use A2007
    Finally I was able to pull it!

    Thanks for all your help.

    I did it in query designer and entered in that data in the field coloumn.

Posting Permissions

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