Results 1 to 4 of 4

Calculating Quantities in a Query?

  1. #1
    chohan78 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    5

    Calculating Quantities in a Query?

    hi, I have a quantity field in a tools table and i want to know that if i issue a tool then system will update the quantity automatically and show me the remaining live balance? i want to create a query and then form based on this.



    thanks

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,566
    Is this a calculated field?
    What exactly does the calculation look like?

    All calculated fields are dynamic, so their value should update as the values that the formulas use change.
    You should be able to confirm this with some simple testing.

    By the way, you will find that many users (myself included) never use the calculated field option in tables, and instead do the calculated fields in queries. This is a fairly new option in Access, and is limited and is not compatible with any other database program (SQL, Oracle, etc). No other ones allow this, so if you ever needed to convert your database from Access to one of these other programs, that part wouldn't work.
    Using Access 2007
    Proficient in Access, Excel, VBA, and SQL

  3. #3
    chohan78 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2015
    Posts
    5
    Can we create a calculated field in a table or only in query?

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    423
    Calculated Fields

    How do you get Access to store the result of a calculation?
    For example, if you have fields named Quantity and UnitPrice, how do you get Access to write Quantity * UnitPrice to another field called Amount?
    The best answer is, "Don't!"
    Calculated fields belong in queries, not tables.

    Calculated columns are part of life on a spreadsheet, but do not belong in a database table. Never store a value that is dependent on other fields: it's a basic rule of normalization. Break the rule, and you have to worry about how to maintain the calculation correctly in every possible circumstance.
    Ask yourself, "Would it be wrong if my field contained a value different from the calculation?" If you answer Yes, you must not store the field. If the difference would be meaningful, you do need the field.
    So, how do you get the calculated field if you do not store it in a table? Use a query:

    1. Create a query based on your table.
    2. Type your expression into the Field row of the query design grid:
      Amount: [Quantity] * [UnitPrice]

    This creates a field named Amount. Any form or report based on this query treats the calculated field like any other, so you can easily sum the results. It is simple, efficient, and foolproof.

    ...


    Even more serious, the calculated results (in a table) are not reliable. If you change the Expression after data has been entered into the table, the existing results may not be updated correctly (through new records are edits are updated), so you cannot rely on the results. A compact/repair does not recalculate, so there is no obvious way to repair the bad results.
    Since you cannot index calculated fields anyway, there is no performance benefit to be gained from using them. You are violating fundamental rules of data normalization for no good purpose. We recommend you use queries for calculated fields, just as in previous versions.

    http://allenbrowne.com/casu-14.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 0
    Last Post: 08-29-2017, 01:49 AM
  2. Replies: 7
    Last Post: 01-29-2014, 01:45 PM
  3. How to sum quantities per group
    By crcastilla in forum Access
    Replies: 2
    Last Post: 03-21-2012, 09:05 AM
  4. Multiple Values, Different Quantities?!
    By Swilliams987 in forum Forms
    Replies: 11
    Last Post: 01-20-2011, 06:35 PM
  5. Adding Quantities?
    By prosbloom225 in forum Reports
    Replies: 0
    Last Post: 09-02-2009, 12:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums