Help Needed: Calculated Fields!

  • Thread starter Thread starter Jen
  • Start date Start date
J

Jen

I am trying to create a query with a simple multiplication of fields, ie
[Amount]*[Unit Cost] to get a Total for each item. I know I need to use a
calculated field in the query, but I have no idea how to do this! I have a
query with all my fields at the top. Where do I put the calculated field,
and how do I put it in?!! I realise this is probably very simple when you
know how to do it, and I apologise for sounding stupid!

Many thanks for any help.
 
In the query grid select the top row of a blank column.
Right click select "build"
In the box select "Tables"
Select the table your fields are in - this should be in the top section of
the query as well by the way.
Select the Amount field (double click)
Click the * icon
Select the Unit Cost field

It should now look like this

[TableName]![Amount]*[TableName]![Unit Cost]

Of course TableName will be that of your table

You could improve this by adding the Nz function (in case any of the field
are empty - now or in the future) like this

Nz([TableName]![Amount],0)*Nz([TableName]![Unit Cost],0)

Good luck
 
I am trying to create a query with a simple multiplication of fields, ie
[Amount]*[Unit Cost] to get a Total for each item. I know I need to use a
calculated field in the query, but I have no idea how to do this! I have a
query with all my fields at the top. Where do I put the calculated field,
and how do I put it in?!! I realise this is probably very simple when you
know how to do it, and I apologise for sounding stupid!

Many thanks for any help.
Click to expand...

Just open the query in design view; in the first blank cell in the Field row
at the top, type your expression. Don't call it Total as that is a reserved
word! Try

ExtCost: [Amount] * [Unit Cost]

Note that if either the amount or unit cost is null you'll get a Null result -
you can use the NZ() function if that's a problem.
 
Back
Top