Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Doing Math In Access Tables

Discussion in 'Information Technology' started by AnthonyOfficeGuy, Oct 30, 2005.

  1. AnthonyOfficeGuy

    AnthonyOfficeGuy
    Expand Collapse
    Guest

    I am creating an inventory database for work. I want one field in the table
    to hold the sum of data in two other fields in the same table (i.e. Total
    Price = Unit Price X Qty). Is it possible to store the result of the math in
    the table itself along OR do I have to do the calculation in the report only?

    Experience: beginner
     
  2. Loading...

    Similar Threads Forum Date
    How To Meditate (dhyaan / Simran) ? Am I Doing It All Wrong. I Can't Concentrate And Feel Sleepy Intellectual Articles Mar 25, 2016
    Conentrating while Doing Paath? Blogs Oct 22, 2015
    Covering Kesh While Eating And Doing Other Activities Questions and Answers Mar 27, 2015
    Doing parkash of SGGS at home? Questions and Answers Mar 31, 2014
    Am I doing something wrong? Sikh Youth Nov 23, 2013

  3. Ofer

    Ofer
    Expand Collapse
    Guest

    You definitly shouldn't store a calculation field in a table, it mean that
    you'll have to maintain it, and it can cause only mistake.
    You can always create a query that will return this value
    Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName

    or make a field in a report return the value, in the control source of the
    field you can write
    =[Unit Price] * [Qty]
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "AnthonyOfficeGuy" wrote:

    > I am creating an inventory database for work. I want one field in the table
    > to hold the sum of data in two other fields in the same table (i.e. Total
    > Price = Unit Price X Qty). Is it possible to store the result of the math in
    > the table itself along OR do I have to do the calculation in the report only?
    >
    > Experience: beginner
     
  4. AnthonyOfficeGuy

    AnthonyOfficeGuy
    Expand Collapse
    Guest

    Thanks for the response. It did help.

    Just curious though. Why is it not ok to store the result of the calculation
    of two fields in a table in another field in that table?

    "Ofer" wrote:

    > You definitly shouldn't store a calculation field in a table, it mean that
    > you'll have to maintain it, and it can cause only mistake.
    > You can always create a query that will return this value
    > Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName
    >
    > or make a field in a report return the value, in the control source of the
    > field you can write
    > =[Unit Price] * [Qty]
    > --
    > If I answered your question, please mark it as an answer. That way, it will
    > stay saved for a longer time, so other can benifit from it.
    >
    > Good luck
    >
    >
    >
    > "AnthonyOfficeGuy" wrote:
    >
    > > I am creating an inventory database for work. I want one field in the table
    > > to hold the sum of data in two other fields in the same table (i.e. Total
    > > Price = Unit Price X Qty). Is it possible to store the result of the math in
    > > the table itself along OR do I have to do the calculation in the report only?
    > >
    > > Experience: beginner
     
  5. Ofer

    Ofer
    Expand Collapse
    Guest

    Because you need to maintain it, an automatic update can't be perform
    directly in the table, you will have to use either a query or a form to do
    that.
    So if a user will update one of the fields [qty] or [price], it mean that he
    has to remembr updating the total field.
    Keeping the calculated field in the table mean that you have to count on
    people to maintain it.

    But a query behave just as a table, when the total will be calculated every
    time you run the query, so you will never have any error.
    --
    If I answered your question, please mark it as an answer. That way, it will
    stay saved for a longer time, so other can benifit from it.

    Good luck



    "AnthonyOfficeGuy" wrote:

    > Thanks for the response. It did help.
    >
    > Just curious though. Why is it not ok to store the result of the calculation
    > of two fields in a table in another field in that table?
    >
    > "Ofer" wrote:
    >
    > > You definitly shouldn't store a calculation field in a table, it mean that
    > > you'll have to maintain it, and it can cause only mistake.
    > > You can always create a query that will return this value
    > > Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName
    > >
    > > or make a field in a report return the value, in the control source of the
    > > field you can write
    > > =[Unit Price] * [Qty]
    > > --
    > > If I answered your question, please mark it as an answer. That way, it will
    > > stay saved for a longer time, so other can benifit from it.
    > >
    > > Good luck
    > >
    > >
    > >
    > > "AnthonyOfficeGuy" wrote:
    > >
    > > > I am creating an inventory database for work. I want one field in the table
    > > > to hold the sum of data in two other fields in the same table (i.e. Total
    > > > Price = Unit Price X Qty). Is it possible to store the result of the math in
    > > > the table itself along OR do I have to do the calculation in the report only?
    > > >
    > > > Experience: beginner
     
  6. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    AnthonyOfficeGuy wrote:
    > Thanks for the response. It did help.
    >
    > Just curious though. Why is it not ok to store the result of the
    > calculation of two fields in a table in another field in that table?


    If you were using Excel and you wanted column C to be the result of column A
    plus column B would it be a better idea to enter =[A1]+[B1] into cell [C1] and
    then copy that expression down the column or would it be better to write a macro
    that walks down the sheet performing the calculation and storing the result in
    column C?

    The former correlates to doing the Access calculation in a query and the latter
    correlates to performing the calculation in a form and then stuffing the result
    into another field of the table.

    --
    I don't check the Email account attached
    to this message. Send instead to...
    RBrandt at Hunter dot com
     
  7. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Fellow Access MVP John Vinson likes to say "Storing calculated data
    generally accomplishes only three things: it wastes disk space, it wastes
    time (a disk fetch is much slower than almost any reasonable calculation),
    and it risks data validity, since once it's stored in a table either the
    Total or one of the fields that goes into the total may be changed, making
    the value WRONG."

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "AnthonyOfficeGuy" <AnthonyOfficeGuy@discussions.microsoft.com> wrote in
    message news:9C4F27A9-C106-4AE0-A112-A0CFE0354F72@microsoft.com...
    > Thanks for the response. It did help.
    >
    > Just curious though. Why is it not ok to store the result of the
    > calculation
    > of two fields in a table in another field in that table?
    >
    > "Ofer" wrote:
    >
    >> You definitly shouldn't store a calculation field in a table, it mean
    >> that
    >> you'll have to maintain it, and it can cause only mistake.
    >> You can always create a query that will return this value
    >> Select [Unit Price],Qty, [Unit Price] * Qty As Total_Price From TableName
    >>
    >> or make a field in a report return the value, in the control source of
    >> the
    >> field you can write
    >> =[Unit Price] * [Qty]
    >> --
    >> If I answered your question, please mark it as an answer. That way, it
    >> will
    >> stay saved for a longer time, so other can benifit from it.
    >>
    >> Good luck
    >>
    >>
    >>
    >> "AnthonyOfficeGuy" wrote:
    >>
    >> > I am creating an inventory database for work. I want one field in the
    >> > table
    >> > to hold the sum of data in two other fields in the same table (i.e.
    >> > Total
    >> > Price = Unit Price X Qty). Is it possible to store the result of the
    >> > math in
    >> > the table itself along OR do I have to do the calculation in the report
    >> > only?
    >> >
    >> > Experience: beginner
     

Share This Page