Access 2003

[mcnaugg]

I am a complete beginner with Access and would like to know how to calculate
total product costs in a form. The fields in the Table I want use are Basic
Cost, Vat @14% and Total Cost.

 

[banem2]

I am a complete beginner with Access and would like to know how to calculate
total product costs in a form.  The fields in the Table I want use are Basic
Cost, Vat @14% and Total Cost.

There are many way to calculate total product cost and it depends how
your form is designed. Here are couple of them:

1) Put all fields on form and one unbound field. In unbound field
write formula like "= [Basic Cost] * [Vat]" (without quotes)
2) Change source of query to SQL. You will see the same design of SQL
like when you create query. Add new field on query grid, like "Total
Cost: [Basic Cost] * [Vat]" (without quotes)
3) Use DLookup function (see Help for details)
4) Use VBA code - as you are beginner you will most likely use first
or second approach

Last, do not store any results of calculation in table. If you change
Basic Cost and forgot to run calculation, you will have wrong Total
Cost. Also you can always calculate Total Cost on the fly at any time
when its needed.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[Guest]

I am a complete beginner with Access and would like to know how to
calculate
total product costs in a form. The fields in the Table I want use are
Basic
Cost, Vat @14% and Total Cost.

 

[mcnaugg]

Hi Branislav

Thanks for the reply

mcnaugg

I am a complete beginner with Access and would like to know how to calculate
total product costs in a form. The fields in the Table I want use are Basic
Cost, Vat @14% and Total Cost.

There are many way to calculate total product cost and it depends how
your form is designed. Here are couple of them:

1) Put all fields on form and one unbound field. In unbound field
write formula like "= [Basic Cost] * [Vat]" (without quotes)
2) Change source of query to SQL. You will see the same design of SQL
like when you create query. Add new field on query grid, like "Total
Cost: [Basic Cost] * [Vat]" (without quotes)
3) Use DLookup function (see Help for details)
4) Use VBA code - as you are beginner you will most likely use first
or second approach

Last, do not store any results of calculation in table. If you change
Basic Cost and forgot to run calculation, you will have wrong Total
Cost. Also you can always calculate Total Cost on the fly at any time
when its needed.

Regards,
Branislav Mihaljev, Microsoft Access MVP

 

[mcnaugg]

Hi Branislav

I have done as you suggested using example 1). I made a simple db using two
fields (product cost and Vat) plus an unbound field (total cost). In the Vat
field I used "=[product cost]/100*14" and in the unbound field "=[Product
Cost] + [Vat]
I had to enter the unbound field into the Form as I do not know how to
insert it into the Table.
When inputting the values into a number of records in the Form all the
values appear correct. However if I look at the Table only the first record
shows the Vat and Total Cost. The remaining records are blank in these two
fields.
If I make a report using the report wizard only the product cost values
appear, no Vat values. Must I create a report in Design View to be able to
se the unbound field.

You tell me not to save any calculation results in a table. How would I
save these results?

Regards

Gareth McNaughton

I am a complete beginner with Access and would like to know how to calculate
total product costs in a form. The fields in the Table I want use are Basic
Cost, Vat @14% and Total Cost.

There are many way to calculate total product cost and it depends how
your form is designed. Here are couple of them:

1) Put all fields on form and one unbound field. In unbound field
write formula like "= [Basic Cost] * [Vat]" (without quotes)
2) Change source of query to SQL. You will see the same design of SQL
like when you create query. Add new field on query grid, like "Total
Cost: [Basic Cost] * [Vat]" (without quotes)
3) Use DLookup function (see Help for details)
4) Use VBA code - as you are beginner you will most likely use first
or second approach

Last, do not store any results of calculation in table. If you change
Basic Cost and forgot to run calculation, you will have wrong Total
Cost. Also you can always calculate Total Cost on the fly at any time
when its needed.

Regards,
Branislav Mihaljev, Microsoft Access MVP