Create Calculated Fields In Access 2010

In Access 2010, you can manipulate data fields in many ways by applying constraints, changing data types, creating relationships and so on, but creating Calculated fields would be of great use, as you can easily calculate field values based on other field’s data in real time. In this post we will guide you through the procedure of creating a Calculated field, which will take values from other fields to evaluate result.

To start out with creating calculated fields, launch Access 2010, navigate to Create tab and click Table.

create

We will be making a simple Marks table, carrying marks of students in different courses and Total field, containing total marks. For this right-click Table1, and click Design View.

design view 1

Enter an appropriate name of table name and click OK.

marks

Start entering field names, i.e, name of courses and under Data Type, click drop-down button to select Number as data type.


data  type number

Change data types of each course field to Number, except of ID (which is unique identifier) and Total field. Under Total data type list options, select Calculated.

calculated field

It will bring-up Expression Builder dialog. For illustration we will be adding all the marks secured in different fields in Total field. For this start entering all the field names enclosed in square brackets, separated by plus sign. Click OK to continue.

calculated field 1

Now close the marks field and save the changes made. From navigation pane, double click the table to open it in datasheet view. Start entering marks obtained and you will see that the on filing out one complete record, Total field will add up marks obtained in corresponding record.

datasheet view

You can also check out previously reviewed guides on How to export Access 2010 table to XML Data file & How to Export database Table to Plain Text in Access 2010.

Advertisement
  • quresh

    how do i update another field in other table with this field.
    please reply

  • Pingback: Create Calculated Field in Pivot Table [Excel 2010]

  • Joelkabe

    I have a need for the ability to cross-foot several fields in the same table entry as described above.  I have Access 2010, but I DO NOT get a Data Type of “Calcualte” to select when defining my fields.  Why does “Calculate” not show on my drop-down?

  • Usman Mir

    I am using Access 2007 and i want to use this calculated field Data type in my small project. Can anybody help me out that how i can get data type Calculate in my Access 2007?
    plz help me
    Rgds,

  • Borwick

    I need Access 2010 to return the number of records found in a particular DB search/report which is then included to a field – Similar to a CountA would be fine. Can you help?

  • naqibullah

    I am using Access 2007 and i want to use this calculated field Data type in my database. Can anybody help me,how i can get data Calculate type in my Access 2007?
    plz help me
    Regards….

  • Pat

    I work in a building department of a local government and I have a form with six calculated fields that I am adding together. I have SQ FT field x PRICE PER SQ FT for six different catagories and then I am adding those six together. I find that I have to place a zero in the fields that do not have any application to this particular permit in order to get a total value to calculate. Does anyone know how I can fix this so I only need to put SQ FT into the fields that apply?

    • Mark Sworn

      Set default value to 0? I know its an old post… But still!