MS Access Math
There is also a dummy (ie not SQL) for this: First, remove the entire column from your table and for this exercise, pretend your table name is "Labor".
Now create a new query and view it in design mode, add all the fields from the Labor table (so you can check that everything works), select an empty field, right-click and select "Build" from the drop-down list. You should now have a Builder Expression window.
Enter a name for the calculated field, for example. laborTotal, follow it with a colon ":", and then select the field names you want to add from the Tables in the lower left corner of the Expression Builder window and double-click each one. Each field will appear in the Expression Builder after "Total:". Now replace each "Expr" with "+". You should see this in the expression builder: "laborTotal: [Labor]! [Labor Cost] + [Labor]! [Labor Hours] + [Labor]! [Vat]". Click OK and run the query - if all goes well, the summary column will display the results.
source to share
If you really need to update the data in the Total column:
UPDATE YourTableName SET [Total] = [Labour Hours] * [Labour Cost] + [VAT]
A MUCH better approach, however, is to remove the Total column completely from your table and create a query:
SELECT [Labour Cost],
[Labour Hours],
[VAT],
[Labour Hours] * [Labour Cost] + [VAT] AS [Total]
FROM YourTableName
The reason this approach is preferred is because it prevents bad information from being accidentally placed in the Total column. In general, data can become corrupted very easily if you are not careful and can be a real pain (if not impossible) to fix. I would suggest you google "Database Normalization" and read a little about it - it will help you a lot.
source to share
-
re your VAT%, I hope I am not stating the obvious, but you have to enter a decimal, eg..175 is 17.5%
-
You can't just add a percentage, it needs to be multiplied by something. And you must include 100% of the original amount. So if you want to add 17.5%, you need to multiply by 1.175. So, let's say that: i) you want 100% to be included automatically (i.e. you only need to enter .175), and that ii) VAT should be added to the entire amount, and iii) by "specify" you mean that you want to receive a VAT request every time you run a request; Then your formula should be:
Total: ([Total Costs]! [Labor Cost] * [General Costs]! [Working Hours] + [Material Cost]) * (1 + [Enter VAT as a decimal amount?])
Scott said:
basically just want to do the calculations so I can display everything on the screen and in the report.
As some have suggested, you can perform the calculation in a saved query and use it as a record source for both your report and form.
But I would advise you not to. I suggest that you complete the calculations as soon as possible. For a report, this would mean that you have control over your report, which, as its controls, provides the calculation you want to perform. So it only runs for lines that are printed at the point at which the page is rendered.
I would suggest the same thing for a form, except that you want to sort or filter by the full field. Calculated fields in forms cannot be used for sorting / filtering and can be annoying how slow they are displayed in some situations. In this case, you will ignore my advice not to put them in the query / record source, because you have a good reason to calculate it for all rows before actually displaying the data.
But in general, do not put calculations in the source of records that you do not intend to work as a group (filtering, sorting, grouping).
source to share
You can place it as a calculated field, in a request, or on a form. Wherever it makes sense, it is simple and has the right level of reuse. If the calculation is a specific form or representation of data, do it there or in the query.
In this case, you can anticipate that the total will be used separately and logically applied to the string, so making it a calculated field is appropriate. Calculated fields have the advantage that (on most systems not sure about MSAccess) they can only be configured to recalculate when data changes.
If you worked through the ratio of quantity to delivery time, that would be something I would put in a request for a specific form / report, as it will never be reused and there might be a heftier calculation.
I would normally choose the computation in a query, unless it relies on the result of another computation, then doing it in the query might become useless, so do it in form / code.
So, in a nutshell, where is it needed for this calculation.
source to share