How to put math expression in query field ?

  • Thread starter Thread starter Will
  • Start date Start date
W

Will

I have a query built on a table.

Fields are as follows...

Price_Each
Quanity
Discount
Total_Price

For Total_Price I would like to enter a forumla in the query or where ever
so the total would automatically be calculated after the user enters the
Price, Quanity & Discount... like...

= (Price * Quanity) * (Discount/100) or whatever.

Problem is I didn't see where to enter such in the fields of the query
builder in Access...

I see "Sort" and "Criteria" but those don't look like fields for the
Expression.

Where do you enter the formula or expression in the Access Query building
form?

Do I put the formula in the Query or in the Form or where? And where does it
go?

An example would be helpful.

thanks for any help.
 
Open the query in design view. Add this to a blank column --

Total: [Price] * [Quanity] * [Discount]/100
 
You can put a formula on the top row - where the field normally goes.
I've never tried what you are attempting, but I have used formula's
there. Just put your cursor in the top row and click on the Expression
Builder. It will give your expression the default name of Expr1, but
you can change that to something more appropriate. The Expression name
will show up as the field name in the query table.

Here is one that I have used.

MidInit: Left$([MidName], 1) + "."
 
Hi Will

In addition to what Karl posted:

(If you press Shift + F2, you'll have a zoom window which makes it easier to
see what you're doing)

Total Price: [Price_Each] * [Quantity] * [Discount] / 100

The Total_Price field in your table is not required as the Total Price can
be calculated in a query, on a form or on a report ... wherever you need it.

Please post back if you have difficulty following the above.

Debra
 
Depends exactly on what you are trying to do. I see 2 possibilities.

1) You have a table with the fields Price, Quantity & Discount and want to
calculate a value for TotalPrice and display it in a query.
2) You have a table with the fields Price, Quantity & Discount. You also
have a field in the table called TotalPrice and want to use the query to
calculate and insert values into the field. (It is usually a bad idea to
store calculated values in a table. I'll tell you how to do this kind of
update but that doesn't mean that you should be doing it in this particular
case.)

In the query designer:
[bracketed] items should correspond to your actual field names, so
change them as needed.
1) Add the appropriate table and the 3 fields.
On the Query menu, be sure "Select" (the default) is selected for query
type.
Create a 4th field for the query. Type the following into the "Field"
row of the 4th column: TotalPrice: ([Price] * [Quantity]) * ([Discount]/100)
Run the query


2) Add the appropriate table and just the TotalPrice field
On the Query menu, select "Update" for query type.
In the "Update To" row of TotalPrice, type: ([Price] * [Quanity]) *
([Discount]/100)
Run the query

Formula notes:
1) If there is any chance that any of those 3 fields might be Null (aka
empty) you should incorporate the nz function into your formula as follows:
(nz([Price],0) * nz([Quantity],0)) * (nz([Discount],0)/100)
If any part of a math operation is Null, the result will be null. The nz
(NulltoZero) function restates Nulls as zero, or any other value you
specify.

2) If you are storing Discount as 0, 10, 20 etc. (for 0, 10%, 20%, etc.
discount), then you probably want something more like:
(nz([Price],0) * nz([Quantity],0)) * (1 - (nz([Discount],0)/100))

HTH,
 
how to put two formulas in one field
formula no.1 IIf([Percentage]>=80,"A+",IIf([Percentage]>=70,"A",IIf([Percentage]>=60,"B",IIf([Percentage]>=50,"C",IIf([Percentage]>=40,"D",IIf([Percentage]>=33,"E","F"))))))
formula no.2
IIf([Percentage]>=80,"A+",IIf([Percentage]>=70,"A",IIf([Percentage]>=60,"B",IIf([Percentage]>=50,"C",IIf([Percentage]>=40,"D","F")))))
Here are some of the table fields.
[ID] [StudentName] [class] [subject1] [subject2] [subject3] [subject4] [ObtainedMarks] [TotalMarks] [Percentage] [grade]
formula no.1 for class 8th,9th,10th
formula no.2 for class 5th,6th,7th
plz any one help me
 
Back
Top