-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Math 101
- Thread starter TKT_BEER
- Start date
First, you shouldn't put a calculated value into a table.
What you should do is remove Extende_Price and calculate it in a query, then use that query wherever you need it.
To do it your way - to update any field, use something like the below for your query.
UPDATE tblName SET Extende_Price=Item_Cost*Item_Quantity;
To do it my way.
SELECT Item_Cost, Item_Quantity, [Item_Cost]*[Item_Quantity] As Extended_Price FROM tblName;
To drop in both - here's the cliff-notes.
Go to queries - hit new. Select Design. Close the table pop-up window.
On the blue bar at the top of the query display, right click it and select SQL View. Paste in the above text. If the field names match exactly the above will work.
You can change it back to design view (right click blue-bar at top) and select Query View to see what it looks like in QBE (Query by Example)
Mike
What you should do is remove Extende_Price and calculate it in a query, then use that query wherever you need it.
To do it your way - to update any field, use something like the below for your query.
UPDATE tblName SET Extende_Price=Item_Cost*Item_Quantity;
To do it my way.
SELECT Item_Cost, Item_Quantity, [Item_Cost]*[Item_Quantity] As Extended_Price FROM tblName;
To drop in both - here's the cliff-notes.
Go to queries - hit new. Select Design. Close the table pop-up window.
On the blue bar at the top of the query display, right click it and select SQL View. Paste in the above text. If the field names match exactly the above will work.
You can change it back to design view (right click blue-bar at top) and select Query View to see what it looks like in QBE (Query by Example)
Mike
Upvote
0
what I would do is have a macro to run AfterUpdate for both the cost and quantity fields (AfterUpdate is under the properties for the text boxes)
the action would be to SetValue of your [Extende_price] field bound on the form to [cost]*[quantity].
the action would be to SetValue of your [Extende_price] field bound on the form to [cost]*[quantity].
Upvote
0
MS Access gives you something called QBE (Query By Example).
It's a "Wizard" interface making it easier for new uers to build SQL statements. By default, when you attempt to create a new query, it starts you in QBE but you can choose to do your query as SQL directly.
My above instructions were to do exactly the latter, paste SQL directly into it to run. Quoting myself:
Go to queries - hit new. Select Design. Close the table pop-up window.
On the blue bar at the top of the query display, right click it and select SQL View. Paste in the above text. If the field names match exactly the above will work.
There are other ways (keystrokes and keyclicks) to do the same thing but the SQL syntax doesn't change.
My suggestion is, figure out how to do it with a basic query first. Then worry about attempting to add buttons to forms as Dr_Worm and jobb are suggesting.
It's a "Wizard" interface making it easier for new uers to build SQL statements. By default, when you attempt to create a new query, it starts you in QBE but you can choose to do your query as SQL directly.
My above instructions were to do exactly the latter, paste SQL directly into it to run. Quoting myself:
Go to queries - hit new. Select Design. Close the table pop-up window.
On the blue bar at the top of the query display, right click it and select SQL View. Paste in the above text. If the field names match exactly the above will work.
There are other ways (keystrokes and keyclicks) to do the same thing but the SQL syntax doesn't change.
My suggestion is, figure out how to do it with a basic query first. Then worry about attempting to add buttons to forms as Dr_Worm and jobb are suggesting.
Upvote
0
Similar threads
- Question
