Microsoft Access A to Z: Creating calculated fields in queries

This short video shows how to create calculated fields in queries for mathematical calculations that use (parentheses) and math operators such as * multiplication, as well as textual calculations that use the & concatenation operator to combine pieces of text. It demonstrates use of the Zoom and Expression Builder dialog boxes to enter and edit expressions as well as how to qualify field names with [table name].[field name] syntax. Finally, it shows how calculated fields in queries are not a duplication of data, but rather, formulas that automatically update as the individual fields that they use in the calculation are updated.
All of my TH-cams can be found at: learncs.w3spac...

[@dalskiBo]

Nicely concise - thanks

[@lfriedrichsen]

You're welcome. See all of my TH-cams organized at learncs.w3spaces.com

[@robertdonnan2893]

This is exactly what I needed. Thank you so much, Lisa.

[@lfriedrichsen]

Glad to hear! Fyi all of my TH-cams are listed at learncs.w3spaces.com

[@owenwaldo]

Is one equation better than the other regarding speed?
I already have: Subtotal: [Quantity]*[Unit Price].
for example,
Discountsubtotal: ([Quantity]*[Unit Price])-([Subtotal]*[Discount]) // this one is doing the subtotal calculation, redundant to the Subtotal column.
vs.
Discountsubtotal: ([Subtotal])-([Subtotal]*[Discount]) // this one is using the subtotal calculation that was already complete.
I see that there are many ways to calculate this query question, but I'm wondering how much thought should go into each query equation individually so to improve performance of a large database. Thank you!!!!

[@lfriedrichsen]

I don't think that you'll notice a difference in the speed unless you're working with hundreds of thousands of records. If so, try it both ways and see if the speed is noticeably different. But from a readability and logic standpoint, if the Subtotal field is already created as [Quantity]*[Unit Price], I'd favor the second formula. Repeating that expression when it's already calculated as Subtotal forces the reader to study that expression to make sure it's the same thing as Subtotal. No need for that.

[@lfriedrichsen]

And a minor thing - you don't need any of the parentheses in the second expression but adding them around ([Subtotal]*[Discount]) does make it easier to read, imo. Take care.

[@Yuzema]

Thank you but I have a question.
To begin with, How did you make the [Order Details] .UnitPrice auto update based on [Order Details] .UnitPrice was entered?

[@lfriedrichsen]

I'm sorry, but I don't understand your question given you're referencing the same field name twice in that question.

[@atef74edu]

That was awesome, as always thank you so much Prof. Lisa, I do have a question here. I like the idea of having another unit price in the ordersdetail table which I think it is a benefit in case if there’s a price increase in the company and if I increase the price in the products table it will affect the orders I already created before the increase. I am sorry, here’s my question when I am creating the orders form how can I let the unit price text box bring the unit price from the product table and if the user leave it the way it is it will be transferred to the unit price in the orders detail but if the user override the standard price and enter a new value access will take that value and store it in the unit price field in the orders detail instead of updating the standard price in the product table.
I am sorry for the long question I wish google around to load a picture in the comments section will be easy to put it in a diagram.
Thank you in advance

[@lfriedrichsen]

Great question. Put both fields on the same form in their own text boxes. Name the text boxes clearly so you know which field is which such as txtOrderDetails_UnitPrice.Value and txtProducts_UnitPrice.Value. Then use VBA to trigger the transfer of the value from the Products table to the Order Details table such as: txtOrderDetails_UnitPrice.Value = txtProducts_UnitPrice.Value

[@lfriedrichsen]

You'll still be able to edit the text box that contains the unit price for the order details table but it will start out with the value of the unit price from the products table.

[@lfriedrichsen]

How/when you trigger the code is based on how you fill out the form. I put it the code on the after update event property of the ProductID field given that information is chosen before we know anything about the product prices. Good luck and see my TH-cam on VBA (it's very short) if you need a little introduction to it.