Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2011
    Posts
    4

    Question Unanswered: Adding a calculated field to a table

    Using microsoft access 2003 who can you add a calulated field from a query into a table.

    Cheers

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    It is very seldom appropriate to store calculated fields in Tables! Best practice is to simply re-calculate the value as needed, for Forms, Queries, Reports, etc. Doing so can lead to invalid data and calculations are almost always faster than retrieving data.

    What kind of calculation are you talking about?

    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

  3. #3
    Join Date
    Feb 2011
    Posts
    4
    my database is based on a shop.

    i need my database to deduct the current sales from the number in stock giving me the new number in stock and then update the stock if its below a certain level

    but since the new number in stock is a calulated field it won't let me update it.

    i thought putting it in a table would solve this problem or is there a easily way around this?

  4. #4
    Join Date
    May 2010
    Posts
    601
    but since the new number in stock is a calulated field it won't let me update it.
    That is how it should work. You should be calculating the number in stock.

    To learn about how to properly hand this see: Inventory Control: Quantity on Hand

    **Note: Inventory control is not a simple task.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  5. #5
    Join Date
    Feb 2011
    Posts
    4
    is their no easier way round it??

    i have a form working that calculates the current number in stock and the number of sales and then takes them away giving me the new number in stock

    then i have a query that checks if the new number in stock is below a allowable number and if not it needs to be re-ordered.

    Next i use a query to add the re-order level to the new number in stock with the criteria set as 'more stock needed' giving me the field 'new stock'

    finally i used a update query to update the number of stock in my orignal table to new stock which i calulated in the next query

    but everytime i try to update the stock levels a message comes up saying it must be a updatable query

    this most be a common problem with most database so is their no simple soultion????

  6. #6
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by stones View Post
    finally i used a update query to update the number of stock in my orignal table to new stock which i calulated in the next query
    This is not a good idea. There isa not audit trail, and other issues.

    You should not be storing calculated values.

    Quote Originally Posted by stones View Post

    this most be a common problem with most database so is their no simple soultion????
    There is a simple solution. You do not store the calculated value. You calculate it as needed.

    I do believe the reason you have having issue is that you are trying to do something that you should not do.

    Inventory control is based on transactions. This is why it is best handle with a database and not a spreadsheet. What you are asking how to do is what is done in a spreadsheet, not a database. function

    *** Inventory Control is very difficult to do correct. ***
    If you try ti shortcut it by not using transaction you will have lots of issue with integrity of the data.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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