MS Access Math

Question

Hi,

I've 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 ?

Many thanks for any help

Scott

Answer 1

You don't need the "Total" column in all probability.

Your queries or reports will probably resemble this:

SELECT [Total] = [Labour Cost] * [Labour Hours] + [VAT]

You can use the same sort of formula in controls on your forms or reports.

Answer 2

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, would be to completely remove the Total column 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 you from accidentally putting bad information in the Total column. In general, data can very easily become corrupted if you're not careful and can be a real pain (if not impossible) to fix. I would suggest you google "Database Normalization" and read up a little on it - this will help you tremendously.

Answer 3

Hi,

Sorry, I'm really new to this. So where do I actually put this information ? Is it in the form itself or a query ?

Thanks for your help.

Scott

Answer 4

You could put it in as a calculated field, in the query or on the form. Wherever makes sense, is simple and has the right level of re-usability. If the calculation is specific a form or view of the data, do it there or in the query.

In this case, you can foresee that the total will be an oft used value and logically applied to the row, so making it a calculated field is appropriate. Calculated fields have the advantage that (in most systems, not sure about MSAccess) they can be set to only re-calculate when the data changes.

If you were working out say the ratio of the quantity vs delivery lead time, that would be more something I would put in the query for the specific form/report as it's never going to be re-used and may be a more hefty calculation.

I would usually choose to do a calculation in the query, unless it relies on the result of another calculation, then doing it in the query could get kinda messy, so do it in the form/code.

So in summary, wherever appropriate for that calc.

Answer 5

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.

Answer 6

Brilliant !!....the kind of answer I like :)

Could I just ask, how would I add VAT onto the total at 17.5% ?

Thanks again

Scott

Answer 7

Create a new query.

add your table

add your three non-total fields to the query

in a new field type:

total: [VAT] + [Labour Hours] * [Labour Cost]

run query

Answer 8

Thanks....

I've got this to work :

Total: [General Expenses]![Labour Cost]*[General Expenses]![Labour Hours]+[Material Cost]+[VAT]

I did it thi sway so that I can specify a VAT amount (the field type is number, percentage. The only thing is that when I put in something like 17.5% it jumps to 100%, if I put 160% it jumps to 200%.

Any ideas ?

Thanks

Answer 9

1. re your VAT %, I hope I'm not stating the obvious, but you should be entering a decimal e.g. .175 is 17.5%

2. You cannot just add a percentage, it must 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, assuming that: i) you want the 100% included automatically (i.e. you will only need to type .175) and that ii) VAT should be added to the entire amount, and that iii) by "specify" you mean that you want to be prompted for the VAT each time the query is run; Then your formula should be:

Total: ([General Expenses]![Labour Cost]*[General Expenses]![Labour Hours]+[Material Cost]) * (1+ [enter VAT as decimal amount?])

Answer 10

Scott said:

basically just want to make the calculations so that I can display the total on screen and in a report.

As some have suggested, you could do the calculation in a saved query and use as the recordsource for both your report and form.

But I'd suggest you not do that. I'd suggest that you do the calculations at the latest possible time. For a report, this would mean you'd have a control on your report that has as its controlsource the calculation you want to perform. That way, it's only performed for the rows that are printed at the point that the page is formated.

I'd suggest the same thing for a form except if you want to sort or filter on the total field. Calculated fields on forms can't be used for sorting/filtering, and can be annoying in how slowly they display in some situations. In that case, you'd ignore my advice to avoid putting them in the query/recordsource, because you have a good reason for calculating it for all rows before the actual display of the data.

But in general, don't put calculations in a recordsource that you're not going to operate on as a group (filtering, sorting, grouping).