How to create field A= field C * fied B

[gatrechicky]

I am a new bie!
I have a table with 3 field, can someone help me on how to create field A=
field C* field B.
Thanks,

 

[Rick Brandt]

I am a new bie!
I have a table with 3 field, can someone help me on how to create
field A= field C* field B.
Thanks,

You don't. You use unbound controls in forms or reports to display the
result of the expression or use a computed field in a query to create it.
Calculations can't be done in tables and for the most part the result of
calculations should not be stored in tables.

 

[a a r o n . k e m p f]

correction!

you can only use this type of calculation if you move to SQL Server.

SQL Server allows calculated / computed columns.
Access doesn't.

So with Access-- you need to copy this business logic into any query
where you want to use it.
With SQL Server- you define it on the table and then consume it
wherver you want

 

[Tony Toews [MVP]]

correction!
incorrect.

you can only use this type of calculation if you move to SQL Server.

SQL Server allows calculated / computed columns.
Access doesn't.

So with Access-- you need to copy this business logic into any query
where you want to use it.
With SQL Server- you define it on the table and then consume it
wherver you want

And what is the problem with using such fields in a query?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[posted_by_anonymous]

back to (e-mail address removed):

He's not only wrong, but dead wrong on this: there are any number of
databases that allow calculated fields/columns in tables, many of them little
more than trash products.

And what is the problem with using such fields in a query?

There is nothing wrong with using Calculated Fields or Calculated Columns in
a Query. That's exactly where they should be used, and that's where data
should be denormalized.

BUT, using them in Tables is a violation of Relational Database design
rules. If The Troll Aaron isn't aware of this, he can go back and read Codd
and Date's books on the subject. The only question is "Why would people as
smart as Microsoft violate RDB design rules in a product like SQL Server?"

Anony Mous

 

[a a r o n . k e m p f]

uh, you need to put the same equation into 20 different queries!

only SQL Server lets you put your logic where it belongs-- IN ONE
PLACE

 

[a a r o n . k e m p f]

just because JET doesn't support computed columns; doesn't make them
bad

 

[Arvin Meyer [MVP]]

message

just because JET doesn't support computed columns; doesn't make
them bad

Sorry, that's totally incorrect. Not only does JET fully support computed
columns, it can also fully resolve VBA functions within those columns, which
is something that SQL-Server does not support.

 

[Arvin Meyer [MVP]]

message

uh, you need to put the same equation into 20 different queries!

only SQL Server lets you put your logic where it belongs-- IN
ONE PLACE

Also incorrect. Most DBMS allow you to call a single instance of a view or
stored procedure. JET allows calling a single query or a VBA function. So
logic can easily be put in a single place.

 

[a a r o n . k e m p f]

IN A TABLE OR IN A QUERY?

SQL Server doesn't need VBA functions; it's got TSQL functions
and .NET functions

thanks

 

[a a r o n . k e m p f]

JET allows you to put it in a single query.
and then when you need anothr copy of it; you copy and paste the
equation.

And then when you need to change the equation- then you need to change
it in multiple queries.

JET just isn't as efficient as SQL Server
I'd rather have calculations like this-- in one place-- than in 20
different queries.

and if you were implying that you can STACK queries on top of queries?
It's just not STABLE using jet.

Sorry-- but your database doesn't compete.

-Aaron

 

[a a r o n . k e m p f]

storing the CALCULATION in a table works much better than storing the
VALUE in a table.. or the CALCULATION In multiple queries

 

[posted_by_anonymous]

uh, you need to put the same equation into 20 different queries!

That's a LIE, aaron, and you knew it before you typed it. Put it in a query
once, that's all.

only SQL Server lets you put your logic where it belongs-- IN ONE
PLACE

Any trash database software that doesn't follow relational rules and allows
calculated columns in tables "allows" the same. Not even Addled aaron would
use most of those. So it's another LIE, and you knew it was a lie before you
typed it, too.

No "Two-Gun Pete" around here, but this post is by "Two-Lie aaron".

Anony Mous

 

[Larry Linson]

and if you were implying that you can STACK
queries on top of queries? It's just not STABLE
using jet.

I am astonished that Microsoft doesn't take legal action against you for
spreading misinformation about their product and interfering with commerce.
Queries can be used as data sources in other Queries in Jet databases, and
Jet is solid and stable.

Sorry-- but your database doesn't compete.

Arguments not backed up by facts are nothing but rants, Mr. Kempf, and this
one is not backed up by any facts... it is blatant misrepresentation and
denigration of a product that is successful beyond expectations because it
works and works well.

Larry Linson
Microsoft Office Access MVP

 

[a a r o n . k e m p f]

yah.. putting it in one query is all fine and good.. until you need to
use the same LOGIC in another query. then you have two copies of the
same equation.
and then you have FOUR copies of the same equation.

and then-- when you need to change it-- you need to look through all
your queries to fix some common equation like 'QtyExtended'-- do you
really want to put this equation in 20 different places?

and for the record, in JET-- stacking queries on top of queries
_ALWAYS_ leads to problems with JET.

with SQL Server-- you can just put the single equation-- where it
belongs-- IN A TABLE and it is computed whenever you need it-- so it
is always current

-Aaron

 

[a a r o n . k e m p f]

yah.. putting it in one query is all fine and good.. until you need to
use the same LOGIC in another query. then you have two copies of the
same equation.
and then you have FOUR copies of the same equation.

and then-- when you need to change it-- you need to look through all
your queries to fix some common equation like 'QtyExtended'-- do you
really want to put this equation in 20 different places?

and for the record, in JET-- stacking queries on top of queries
_ALWAYS_ leads to problems with JET.

with SQL Server-- you can just put the single equation-- where it
belongs-- IN A TABLE and it is computed whenever you need it-- so it
is always current

-Aaron

 

[a a r o n . k e m p f]

and for the record, JET is stupid; as is anyone that uses it.

 

[Arvin Meyer [MVP]]

message

IN A TABLE OR IN A QUERY?

SQL Server doesn't need VBA functions; it's got TSQL functions
and .NET functions

That would be a query, of course, calculations should never run in tables.
..NET doesn't come with or run in Access front-ends, so you haven't provided
a solution to the question.

 

[Arvin Meyer [MVP]]

But if you really knew anything about JET or Access in general, you'd know
that you can write a function, in a Standard Module and call it from
anywhere. And if you ever needed to change the function, you'd change it in
a SINGLE place. Further, you can also put an expression in a JET table and
use the Eval() function in a query, form, report, or within another
function.

And for the record, in JET-- stacking queries on top of queries _NEVER_
leads to problems with JET. I've done it for 15 years now, sometimes piling
them as many as 6 deep and NEVER had a problem ... NOT EVEN ONCE.

An old adage, passed down in our family for years, states, "It is better to
keep your mouth shut and let the world think that you are stupid, than to
open your mouth and prove it" You would do well to follow that.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

message
yah.. putting it in one query is all fine and good.. until you need to
use the same LOGIC in another query. then you have two copies of the
same equation.
and then you have FOUR copies of the same equation.

and then-- when you need to change it-- you need to look through all
your queries to fix some common equation like 'QtyExtended'-- do you
really want to put this equation in 20 different places?

and for the record, in JET-- stacking queries on top of queries
_ALWAYS_ leads to problems with JET.

with SQL Server-- you can just put the single equation-- where it
belongs-- IN A TABLE and it is computed whenever you need it-- so it
is always current

-Aaron

 

[a a r o n . k e m p f]

you can't LINK to that function, you can only IMPORT it

with SQL Server, you keep your FUNCTIONS and QUERIES and TABLES where
they belong-- on a database SERVER. So that anyone can use new queries
without a new build.
It allows you to do a lot more dynamic development-- changing a query
in production without putting out a new build?

PRICELESS