Calculate field with input from different table

[Andreas]

Hi all,

I would like to calculate a value of a field in table A via a formula.
However, some input for this formula contains a field in table B.

To put differently, in table A I would like to set the data type of
one field to "calculated" and use a field from table B within the
formula. Unfortunately, Access 2007 only offers me all the fields
within table A for incorporation in my formula. How can I use a field
from a different table for a calculated field in another table?

Best,
Andreas

 

[Armen Stein]

I would like to calculate a value of a field in table A via a formula.
However, some input for this formula contains a field in table B.

To put differently, in table A I would like to set the data type of
one field to "calculated" and use a field from table B within the
formula. Unfortunately, Access 2007 only offers me all the fields
within table A for incorporation in my formula. How can I use a field
from a different table for a calculated field in another table?

You can't do this directly in the table.

You can join the tables together in a query and calculate it there.
That's a more normalized approach anyway - you usually don't want to
store a calculated value.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jeff Boyce]

Andreas

Are you saying that you wish to use data you already have in tables in
Access, do some calculations, and store that calculated value (redundantly)
in a table in Access? If so, why? It is rarely necessary to store
calculated values.

Instead, if you need the calculated value, use a query and do the
calculation there, in real time. That way, if any of the underlying data
changes, your query always has the correct value.

If you believe you have one of the relatively rare situations in which
storing a calculated value is appropriate, you'll need to provide us more
specific descriptions so we can offer more specific suggestions.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Gerald W.Olson]

Hi all,

I would like to calculate a value of a field in table A via a formula.
However, some input for this formula contains a field in table B.

To put differently, in table A I would like to set the data type of
one field to "calculated" and use a field from table B within the
formula. Unfortunately, Access 2007 only offers me all the fields
within table A for incorporation in my formula. How can I use a field
from a different table for a calculated field in another table?

Best,
Andreas

 

[Andreas]

Andreas

Are you saying that you wish to use data you already have in tables in
Access, do some calculations, and store that calculated value (redundantly)
in a table in Access?  If so, why?  It is rarely necessary to store
calculated values.

Instead, if you need the calculated value, use a query and do the
calculation there, in real time.  That way, if any of the underlying data
changes, your query always has the correct value.

If you believe you have one of the relatively rare situations in which
storing a calculated value is appropriate, you'll need to provide us more
specific descriptions so we can offer more specific suggestions.

Well, I am not exactly sure how I should approach my problem. I am
developing a database in order to store and evaluate my stock trades.
I would like to set up an ID for each trade. The ID should contain
different values, such as the underlying security (e.g. stock, option
etc), the trade date, the stock ticker and entry level in order to
create an individual ID. I would like to have the ID this way, so that
I can use this ID value in order to save a JPG chart under this
particular name. This helps me to bring some order in all my JPG-
charts.

My concrete problem is that the following: Within the trade record, I
just enter the ISIN for a specific intstrument (via a lookup field)
that I traded. Related information for this ISIN (e.g. such as
underlying security) are stored in a different table. Now, I would
need this information in order to create the ID appriately. I am not
sure what other possibilities I have in order to create a meaningful
trade ID. Any suggestions are welcome.

Best,
Andreas

 

[Jeff Boyce]

Actually, you do NOT want to create that kind of "intelligent" ID. Trying
to stuff multiple facts into one field is not good database design, not to
mention the difficulty of keeping a calculated field "in sync" with all of
the fields that make it up.

Instead, a simple solution... Use one field for each fact (good database
design), then use a query to concatenate which ever fields you want. That
will give you a way to "name" your charts...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Andreas

Are you saying that you wish to use data you already have in tables in
Access, do some calculations, and store that calculated value
(redundantly)
in a table in Access? If so, why? It is rarely necessary to store
calculated values.

Instead, if you need the calculated value, use a query and do the
calculation there, in real time. That way, if any of the underlying data
changes, your query always has the correct value.

If you believe you have one of the relatively rare situations in which
storing a calculated value is appropriate, you'll need to provide us more
specific descriptions so we can offer more specific suggestions.

Well, I am not exactly sure how I should approach my problem. I am
developing a database in order to store and evaluate my stock trades.
I would like to set up an ID for each trade. The ID should contain
different values, such as the underlying security (e.g. stock, option
etc), the trade date, the stock ticker and entry level in order to
create an individual ID. I would like to have the ID this way, so that
I can use this ID value in order to save a JPG chart under this
particular name. This helps me to bring some order in all my JPG-
charts.

My concrete problem is that the following: Within the trade record, I
just enter the ISIN for a specific intstrument (via a lookup field)
that I traded. Related information for this ISIN (e.g. such as
underlying security) are stored in a different table. Now, I would
need this information in order to create the ID appriately. I am not
sure what other possibilities I have in order to create a meaningful
trade ID. Any suggestions are welcome.

Best,
Andreas