I know I should know the answer to this but if you are creating a query that includes individual fields as well as calculations based on some of those fields, do you select the source field or the query "field" when using the expression builder?
Quick Question (1 Viewer)
- Thread starter ahuvas
- Start date
Generally you would do it in the query.
Place all the fields you are going to require for your end result (form/report) into the query then create a calculated field. You do this by going to the right with the query in design view and in the first blank box enter the name of the calculated field, followed by a colon followed by the math.
Example: If you wanted to know your profit margin on a product you would enter:
Profit: ([ListPrice]-[OurCost])
Place all the fields you are going to require for your end result (form/report) into the query then create a calculated field. You do this by going to the right with the query in design view and in the first blank box enter the name of the calculated field, followed by a colon followed by the math.
Example: If you wanted to know your profit margin on a product you would enter:
Profit: ([ListPrice]-[OurCost])
I think we are talking about the same thing however when using the expression builder in query and you have to refer to list price and ourcost in the expression if gives you a chance to get those values from the query, table, forms etc..in the first column of the expression builder. do yuo use the field that appears under the query or the field from the "origina source" which would be your table?
You would have to include the fields you wish to use in the calculation in the query.
The source of the data would usually be the table although you could use another query as the source. This is called a sub query.
I am attempting to tell you that you do not require the expression builder. Just enter the text in the previous message
Profit: ([ListPrice]-[OurCost])
directly into the first blank box in the query while in design view.
The source of the data would usually be the table although you could use another query as the source. This is called a sub query.
I am attempting to tell you that you do not require the expression builder. Just enter the text in the previous message
Profit: ([ListPrice]-[OurCost])
directly into the first blank box in the query while in design view.
I understnad that but that is quite a simple example... Lets assume that I was using the expression builder for whatever reason. I referred to those fields already earlier in the query (each of those fields were already a column) do I then refer back to the fields in the original source table or the fields in the query?
Brianwarnock
Retired
- Local time
- Today, 16:11
- Joined
- Jun 2, 2003
- Messages
- 12,701
All fields used in a query come from the table, you will see the From clause in the SQL. The only difference when using the builder is that the query must first be saved for you to be able to select a field which must have first been selected in the Select clause, which you have.
Oh and selecting from the table the above would say
Profit: ([tablename]![ListPrice]-[Tablename]![OurCost])
Brian
