how do i insert a formula in microsoft access?

Discussion in 'Microsoft Access Database Table Design' started by Guest, Jan 15, 2006.

  1. Guest

    Guest Guest

    i have created a new project in table and am on design view and was wondering
    on how to insert a formula.

    please reply a.s.a.p

    thanks.
     
    Guest, Jan 15, 2006
    #1
    1. Advertisements

  2. Guest

    Allen Browne Guest

    In Access, you put the formula into the field in a query, not in the table.

    Create the table without this field.
    Then create a query, and type the calculation into the Field row.

    More info and example:
    Calculated fields
    at:
    http://allenbrowne.com/casu-14.html

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "ash" <> wrote in message
    news:...
    >i have created a new project in table and am on design view and was
    >wondering
    > on how to insert a formula.
    >
    > please reply a.s.a.p
    >
    > thanks.
     
    Allen Browne, Jan 15, 2006
    #2
    1. Advertisements

  3. Just to amplify on Allen's advise.

    You MUST put the formula into the field in a query, not in the table. Tables
    cannot accept formulae.

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


    "Allen Browne" <> wrote in message
    news:...
    > In Access, you put the formula into the field in a query, not in the
    > table.
    >
    > Create the table without this field.
    > Then create a query, and type the calculation into the Field row.
    >
    > More info and example:
    > Calculated fields
    > at:
    > http://allenbrowne.com/casu-14.html
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "ash" <> wrote in message
    > news:...
    >>i have created a new project in table and am on design view and was
    >>wondering
    >> on how to insert a formula.
    >>
    >> please reply a.s.a.p
    >>
    >> thanks.

    >
    >
     
    Douglas J. Steele, Jan 15, 2006
    #3
  4. Guest

    Tom Ellison Guest

    The statement "Tables cannot accept formulae" is true enough for Jet, but
    not for MSDE/SQL Server (which is distributed with every version of Access
    since 2000). This can be a valuable tool. It's a calculated column that
    updates itself automatically as the values on which it depends are updated.
    It's greatest value may be in the ability to index on the result. Other
    than that, you could pretty much just put the formula into any query.
    However, if that formula may be subject to later change, then the modular
    nature of putting that formula into one place where it can be modified later
    to change the whole application becomes very important indeed.

    Tom Ellison


    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%...
    > Just to amplify on Allen's advise.
    >
    > You MUST put the formula into the field in a query, not in the table.
    > Tables cannot accept formulae.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no private e-mails, please)
    >
    >
    > "Allen Browne" <> wrote in message
    > news:...
    >> In Access, you put the formula into the field in a query, not in the
    >> table.
    >>
    >> Create the table without this field.
    >> Then create a query, and type the calculation into the Field row.
    >>
    >> More info and example:
    >> Calculated fields
    >> at:
    >> http://allenbrowne.com/casu-14.html
    >>
    >> --
    >> Allen Browne - Microsoft MVP. Perth, Western Australia.
    >> Tips for Access users - http://allenbrowne.com/tips.html
    >> Reply to group, rather than allenbrowne at mvps dot org.
    >>
    >> "ash" <> wrote in message
    >> news:...
    >>>i have created a new project in table and am on design view and was
    >>>wondering
    >>> on how to insert a formula.
    >>>
    >>> please reply a.s.a.p
    >>>
    >>> thanks.

    >>
    >>

    >
    >
     
    Tom Ellison, Jan 16, 2006
    #4
  5. It's seldom recommended that you store computed values in tables. Not only
    is it officially a violation of relational database normalization rules, but
    as 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."

    Put the formulae in a query, and use the query wherever you would otherwise
    have used the table.

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


    "bungie" <> wrote in message
    news:...
    >
    > Same as ash, I have created my tables and forms (this is all new,
    > MASSIVE learning curve here) got my formula's working in my form, but
    > how do I then place the results into the field in the table that I
    > want. Or am I missing something obvious here ?
    >
    > bungie
    >
    >
    > --
    > bungie
    > ------------------------------------------------------------------------
    > bungie's Profile: http://www.hightechtalks.com/m855
    > View this thread: http://www.hightechtalks.com/t2332714
    >
     
    Douglas J. Steele, Jan 29, 2006
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Dariusz Tyszka

    VB using ADO to access Microsoft Access 97.

    Dariusz Tyszka, Aug 11, 2003, in forum: Microsoft Access Database Table Design
    Replies:
    3
    Views:
    806
    Douglas J. Steele
    Aug 12, 2003
  2. Guest

    I need microsoft office2003/access, how do I download access?

    Guest, Jul 12, 2005, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    213
    John Vinson
    Jul 12, 2005
  3. Guest

    how to insert a microsoft word into a form of a database

    Guest, Jan 7, 2006, in forum: Microsoft Access Database Table Design
    Replies:
    4
    Views:
    171
    Vincent Johns
    Jan 14, 2006
  4. Guest

    Microsoft Access 2000 formula question

    Guest, Mar 12, 2007, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    187
    Allen Browne
    Mar 12, 2007
  5. Guest

    how can i use Microsoft Offfice to create Microsoft access templat

    Guest, Apr 4, 2007, in forum: Microsoft Access Database Table Design
    Replies:
    1
    Views:
    241
    Rick B
    Apr 4, 2007
Loading...

Share This Page