Welcome to SPN

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

Sign Up Now!

MS Access 2003

Discussion in 'Information Technology' started by Russ, Jul 28, 2006.

Tags:
  1. Russ

    Russ
    Expand Collapse
    Guest

    Real simple. How do I do calculations in Access? In my table, I have a Cost,
    Tax, Shipping, and Total Cost. I want to have the sum of all fields in my
    Total Cost field. How do I do that? Thank you.
     
  2. Loading...


  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    It's not recommended to have a total(calculated) field actually in the
    table, (one of the rules of data normalisation).

    Create a query based on your table, create a formula field:

    TotalCost: [Cost] + [Tax] + [Shipping]



    "Russ" <tom@tom.com> wrote in message
    news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    > Real simple. How do I do calculations in Access? In my table, I have a
    > Cost, Tax, Shipping, and Total Cost. I want to have the sum of all fields
    > in my Total Cost field. How do I do that? Thank you.
    >
     
  4. Russ

    Russ
    Expand Collapse
    Guest

    Thanks for your reply. Where do I enter that formula?

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    > It's not recommended to have a total(calculated) field actually in the
    > table, (one of the rules of data normalisation).
    >
    > Create a query based on your table, create a formula field:
    >
    > TotalCost: [Cost] + [Tax] + [Shipping]
    >
    >
    >
    > "Russ" <tom@tom.com> wrote in message
    > news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >> Real simple. How do I do calculations in Access? In my table, I have a
    >> Cost, Tax, Shipping, and Total Cost. I want to have the sum of all fields
    >> in my Total Cost field. How do I do that? Thank you.
    >>

    >
    >
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    You need to create a new Query based on your table.
    You would enter the formula as a calculated field in this query.

    Cheers,

    Chris.

    "Russ" <tom@tom.com> wrote in message
    news:ee7cwqcoGHA.5104@TK2MSFTNGP04.phx.gbl...
    > Thanks for your reply. Where do I enter that formula?
    >
    > "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    > news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    >> It's not recommended to have a total(calculated) field actually in the
    >> table, (one of the rules of data normalisation).
    >>
    >> Create a query based on your table, create a formula field:
    >>
    >> TotalCost: [Cost] + [Tax] + [Shipping]
    >>
    >>
    >>
    >> "Russ" <tom@tom.com> wrote in message
    >> news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >>> Real simple. How do I do calculations in Access? In my table, I have a
    >>> Cost, Tax, Shipping, and Total Cost. I want to have the sum of all
    >>> fields in my Total Cost field. How do I do that? Thank you.
    >>>

    >>
    >>

    >
    >
     
  6. Russ

    Russ
    Expand Collapse
    Guest

    Where is this calculated field? Where in the query would I place the below
    formula?

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:WuWdnbU7fOZx9DPZnZ2dnUVZ8tKdnZ2d@bt.com...
    > You need to create a new Query based on your table.
    > You would enter the formula as a calculated field in this query.
    >
    > Cheers,
    >
    > Chris.
    >
    > "Russ" <tom@tom.com> wrote in message
    > news:ee7cwqcoGHA.5104@TK2MSFTNGP04.phx.gbl...
    >> Thanks for your reply. Where do I enter that formula?
    >>
    >> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >> news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    >>> It's not recommended to have a total(calculated) field actually in the
    >>> table, (one of the rules of data normalisation).
    >>>
    >>> Create a query based on your table, create a formula field:
    >>>
    >>> TotalCost: [Cost] + [Tax] + [Shipping]
    >>>
    >>>
    >>>
    >>> "Russ" <tom@tom.com> wrote in message
    >>> news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >>>> Real simple. How do I do calculations in Access? In my table, I have a
    >>>> Cost, Tax, Shipping, and Total Cost. I want to have the sum of all
    >>>> fields in my Total Cost field. How do I do that? Thank you.
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  7. ChrisM

    ChrisM
    Expand Collapse
    Guest

    In the query design form, you have your Table at the top and you can drag
    your fields into the query grid, Ok?
    In a blank column, type the formula as I've shown.

    If this is not making sense, you should really read the helpfiles within
    Access on creating a query.

    Cheers,

    Chris.


    "Russ" <tom@tom.com> wrote in message
    news:%23quirOdoGHA.4912@TK2MSFTNGP05.phx.gbl...
    > Where is this calculated field? Where in the query would I place the below
    > formula?
    >
    > "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    > news:WuWdnbU7fOZx9DPZnZ2dnUVZ8tKdnZ2d@bt.com...
    >> You need to create a new Query based on your table.
    >> You would enter the formula as a calculated field in this query.
    >>
    >> Cheers,
    >>
    >> Chris.
    >>
    >> "Russ" <tom@tom.com> wrote in message
    >> news:ee7cwqcoGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>> Thanks for your reply. Where do I enter that formula?
    >>>
    >>> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >>> news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    >>>> It's not recommended to have a total(calculated) field actually in the
    >>>> table, (one of the rules of data normalisation).
    >>>>
    >>>> Create a query based on your table, create a formula field:
    >>>>
    >>>> TotalCost: [Cost] + [Tax] + [Shipping]
    >>>>
    >>>>
    >>>>
    >>>> "Russ" <tom@tom.com> wrote in message
    >>>> news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >>>>> Real simple. How do I do calculations in Access? In my table, I have a
    >>>>> Cost, Tax, Shipping, and Total Cost. I want to have the sum of all
    >>>>> fields in my Total Cost field. How do I do that? Thank you.
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  8. Russ

    Russ
    Expand Collapse
    Guest

    Thank you! Now I see. it works perfectly fine. Let me ask you this. My first
    column is PO# and I set it up with autonumber. As you know, the first entry
    starts with the number 1. Is there way I can customize that so the first
    entry is INDS001 and then the next field will automatically use INDS002?

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:fr2dnVWQT_nn6DPZRVnyuA@bt.com...
    > In the query design form, you have your Table at the top and you can drag
    > your fields into the query grid, Ok?
    > In a blank column, type the formula as I've shown.
    >
    > If this is not making sense, you should really read the helpfiles within
    > Access on creating a query.
    >
    > Cheers,
    >
    > Chris.
    >
    >
    > "Russ" <tom@tom.com> wrote in message
    > news:%23quirOdoGHA.4912@TK2MSFTNGP05.phx.gbl...
    >> Where is this calculated field? Where in the query would I place the
    >> below formula?
    >>
    >> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >> news:WuWdnbU7fOZx9DPZnZ2dnUVZ8tKdnZ2d@bt.com...
    >>> You need to create a new Query based on your table.
    >>> You would enter the formula as a calculated field in this query.
    >>>
    >>> Cheers,
    >>>
    >>> Chris.
    >>>
    >>> "Russ" <tom@tom.com> wrote in message
    >>> news:ee7cwqcoGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>>> Thanks for your reply. Where do I enter that formula?
    >>>>
    >>>> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >>>> news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    >>>>> It's not recommended to have a total(calculated) field actually in the
    >>>>> table, (one of the rules of data normalisation).
    >>>>>
    >>>>> Create a query based on your table, create a formula field:
    >>>>>
    >>>>> TotalCost: [Cost] + [Tax] + [Shipping]
    >>>>>
    >>>>>
    >>>>>
    >>>>> "Russ" <tom@tom.com> wrote in message
    >>>>> news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >>>>>> Real simple. How do I do calculations in Access? In my table, I have
    >>>>>> a Cost, Tax, Shipping, and Total Cost. I want to have the sum of all
    >>>>>> fields in my Total Cost field. How do I do that? Thank you.
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  9. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Excellent!

    In answer to your second question. Don't think so. Autonumber is exactly
    that, a number.
    The only way to do that would be some VBA code to generate the Purchase
    Order numbers,
    or let the system generate 1,2,3... and use another formula to pad it with
    zeros and stick the INDS in front.
    Look at how to format a number in the Access online help files.

    Cheers,

    Chris.

    "Russ" <tom@tom.com> wrote in message
    news:OqeskhdoGHA.3636@TK2MSFTNGP03.phx.gbl...
    > Thank you! Now I see. it works perfectly fine. Let me ask you this. My
    > first column is PO# and I set it up with autonumber. As you know, the
    > first entry starts with the number 1. Is there way I can customize that so
    > the first entry is INDS001 and then the next field will automatically use
    > INDS002?
    >
    > "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    > news:fr2dnVWQT_nn6DPZRVnyuA@bt.com...
    >> In the query design form, you have your Table at the top and you can drag
    >> your fields into the query grid, Ok?
    >> In a blank column, type the formula as I've shown.
    >>
    >> If this is not making sense, you should really read the helpfiles within
    >> Access on creating a query.
    >>
    >> Cheers,
    >>
    >> Chris.
    >>
    >>
    >> "Russ" <tom@tom.com> wrote in message
    >> news:%23quirOdoGHA.4912@TK2MSFTNGP05.phx.gbl...
    >>> Where is this calculated field? Where in the query would I place the
    >>> below formula?
    >>>
    >>> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >>> news:WuWdnbU7fOZx9DPZnZ2dnUVZ8tKdnZ2d@bt.com...
    >>>> You need to create a new Query based on your table.
    >>>> You would enter the formula as a calculated field in this query.
    >>>>
    >>>> Cheers,
    >>>>
    >>>> Chris.
    >>>>
    >>>> "Russ" <tom@tom.com> wrote in message
    >>>> news:ee7cwqcoGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>>>> Thanks for your reply. Where do I enter that formula?
    >>>>>
    >>>>> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >>>>> news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    >>>>>> It's not recommended to have a total(calculated) field actually in
    >>>>>> the table, (one of the rules of data normalisation).
    >>>>>>
    >>>>>> Create a query based on your table, create a formula field:
    >>>>>>
    >>>>>> TotalCost: [Cost] + [Tax] + [Shipping]
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>> "Russ" <tom@tom.com> wrote in message
    >>>>>> news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >>>>>>> Real simple. How do I do calculations in Access? In my table, I have
    >>>>>>> a Cost, Tax, Shipping, and Total Cost. I want to have the sum of all
    >>>>>>> fields in my Total Cost field. How do I do that? Thank you.
    >>>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     
  10. Russ

    Russ
    Expand Collapse
    Guest

    OK. Thank you for your help Chris.

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:TdednVzVPohRHzPZRVnyuw@bt.com...
    > Excellent!
    >
    > In answer to your second question. Don't think so. Autonumber is exactly
    > that, a number.
    > The only way to do that would be some VBA code to generate the Purchase
    > Order numbers,
    > or let the system generate 1,2,3... and use another formula to pad it with
    > zeros and stick the INDS in front.
    > Look at how to format a number in the Access online help files.
    >
    > Cheers,
    >
    > Chris.
    >
    > "Russ" <tom@tom.com> wrote in message
    > news:OqeskhdoGHA.3636@TK2MSFTNGP03.phx.gbl...
    >> Thank you! Now I see. it works perfectly fine. Let me ask you this. My
    >> first column is PO# and I set it up with autonumber. As you know, the
    >> first entry starts with the number 1. Is there way I can customize that
    >> so the first entry is INDS001 and then the next field will automatically
    >> use INDS002?
    >>
    >> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >> news:fr2dnVWQT_nn6DPZRVnyuA@bt.com...
    >>> In the query design form, you have your Table at the top and you can
    >>> drag your fields into the query grid, Ok?
    >>> In a blank column, type the formula as I've shown.
    >>>
    >>> If this is not making sense, you should really read the helpfiles within
    >>> Access on creating a query.
    >>>
    >>> Cheers,
    >>>
    >>> Chris.
    >>>
    >>>
    >>> "Russ" <tom@tom.com> wrote in message
    >>> news:%23quirOdoGHA.4912@TK2MSFTNGP05.phx.gbl...
    >>>> Where is this calculated field? Where in the query would I place the
    >>>> below formula?
    >>>>
    >>>> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >>>> news:WuWdnbU7fOZx9DPZnZ2dnUVZ8tKdnZ2d@bt.com...
    >>>>> You need to create a new Query based on your table.
    >>>>> You would enter the formula as a calculated field in this query.
    >>>>>
    >>>>> Cheers,
    >>>>>
    >>>>> Chris.
    >>>>>
    >>>>> "Russ" <tom@tom.com> wrote in message
    >>>>> news:ee7cwqcoGHA.5104@TK2MSFTNGP04.phx.gbl...
    >>>>>> Thanks for your reply. Where do I enter that formula?
    >>>>>>
    >>>>>> "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    >>>>>> news:u62dnYgYs9n3_zPZRVnyhQ@bt.com...
    >>>>>>> It's not recommended to have a total(calculated) field actually in
    >>>>>>> the table, (one of the rules of data normalisation).
    >>>>>>>
    >>>>>>> Create a query based on your table, create a formula field:
    >>>>>>>
    >>>>>>> TotalCost: [Cost] + [Tax] + [Shipping]
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>> "Russ" <tom@tom.com> wrote in message
    >>>>>>> news:%23a4g5ZcoGHA.2464@TK2MSFTNGP03.phx.gbl...
    >>>>>>>> Real simple. How do I do calculations in Access? In my table, I
    >>>>>>>> have a Cost, Tax, Shipping, and Total Cost. I want to have the sum
    >>>>>>>> of all fields in my Total Cost field. How do I do that? Thank you.
    >>>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>
    >>>>>>
    >>>>>
    >>>>>
    >>>>
    >>>>
    >>>
    >>>

    >>
    >>

    >
    >
     

Share This Page