Search code examples
sqlms-accesscalculated-columns

MS Access multiply fields

I have got a table in MS Access 2007 with 4 fields.

  • Labour Cost
  • Labour Hours
  • Vat
  • Total

How do I multiply 'Labour Hours' by 'Labour Cost' add the 'VAT' and display the answer in 'Total'

Where would I put any formulas?, in a form or query or table ?

Solution

  • There is also the dummies (ie not SQL) way to do it: First delete your total column from your table and for this exercise pretend that the name of your table is "Labour" .

    Now create a new query and view it in design view, add all the fields from your Labour table (so you can check that everything is working), select an empty field, right click and select "Build" from the drop down list. You should now have an Expression Builder window.

    Type in the name for your calculated field, e.g. labourTotal, follow it with a colon ":" and then select the field names you want to add from Tables at the bottom left of the Expression Builder window and double-click on each. Each field will appear in the expression builder following the "Total:". Now replace each "«Expr»" with a "+". You should see this in the expression builder: "labourTotal: [Labour]![Labour Cost] + [Labour]![Labour Hours] + [Labour]![Vat] ". Click OK and run the query - if all is well the total column will display the results.