Calculate field with input from different table

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
1
Andreas
12/9/2009 9:27:22 PM
access 16762 articles. 3 followers. Follow

5 Replies
12987 Views

Similar Articles

[PageSpeed] 41

On Wed, 9 Dec 2009 13:27:22 -0800 (PST), Andreas
<andreas.vester@gmail.com> wrote:

>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
 
0
Armen
12/9/2009 9:53:42 PM
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.

"Andreas" <andreas.vester@gmail.com> wrote in message 
news:a36b4e4f-5ecf-4cb2-ae13-a699a84a0069@j14g2000yqm.googlegroups.com...
> 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 


0
Jeff
12/9/2009 11:19:10 PM
"Andreas" <andreas.vester@gmail.com> wrote in message 
news:a36b4e4f-5ecf-4cb2-ae13-a699a84a0069@j14g2000yqm.googlegroups.com...
> 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 

0
Gerald
12/10/2009 8:24:25 AM
On 10 Dez., 00:19, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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 (redundantl=
y)
> in a table in Access? =A0If so, why? =A0It 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. =A0That way, if any of the underlying da=
ta
> 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
0
Andreas
12/12/2009 12:49:18 PM
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" <andreas.vester@gmail.com> wrote in message 
news:24ac25fd-0d4f-419e-90bf-526916f53475@d10g2000yqh.googlegroups.com...
On 10 Dez., 00:19, "Jeff Boyce" <nonse...@nonsense.com> wrote:
> 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 


0
Jeff
12/14/2009 4:13:14 PM
Reply:

Similar Artilces:

Receipt template
Hi! I'm making custom receipt template. I use different headers ("OverlayFirstPage" and "OverlayOnlyPage" differs from "OverlayMiddlePage" and "OverlayLastPage"). Problem is that these headers are of quite different size and there is a lot of space between header and document section on middle and last page. Can I define different <TOP> parameters in <DOCUMENT> depending on what page it is (or what overlay uses)? Thanks, Koit I got it myself, using page number: <IF> <CONDITION>\p=1 | \p=\t</CONDITION> <T...

Can I Edit the From Field?
I now receive my voice mail in the forum of email messages with attached wav files. The "from" field is the generic address of the voice mail system. I would like to be able to edit the from field and change it to the name of the person who left the voice mail, so that I can find it later if I need to. If the answer is not possible, I'd like to hear suggestions about the best way to handle this. I know there are other ways I could handle this such as assigning the email to a contact, The issue though is that I would like whatever method I use to work whether I keep the...

Writting ACCESS table to Excel
I am trying to write to Excel and have this code.. which fails at line Dim Rst1 As New ADODB.Recordset, saying "User definded type not defined" . Also after witrring I want to rename my template Any thoughts? Sub xl() Dim DXrptPath As String DXrptPath = "r:\temp\Resultsreportingworksheet.xls" '***Opening Report Template Dim myDB As Database Set myDB = CurrentDb Set ExcelWindow = CreateObject("excel.application") ExcelWindow.Visible = True ExcelWindow.Workbooks.Open (DXrptPath) Dim DXrpt As Workbook Set DXrpt = ActiveWorkbook DXrpt.A...

unique key in an excel 2007 table
Hello, In an excel 2007 table, I want that one column has unique values. I want to check this at input time, with a data validation function. Say that I have Table1 a b qw 1 as 2 zx 3 fg 4 where a and b are column names. I don't want another "qw" or "zx" value in column a. I have tried with Data/Data Validation, with a formula like =(countif([a];[a])=1) but the countif function does not seem to recognaize structured references in data validation formulas. How could I get around this problem? Thank you Hi Try =COUNTIF($A:$A;A1)=1 -- Regards Roger Govier &qu...

count different instances in two columns
Hello all, I haven't been able to solve this... I have two columns in the same sheet. I would like to extract (in another sheet) all the unique elements of each column and count how many each data is repeated. Any element can appear in both column. An example would be: column 1 column 2 red red red blue blue green yellow magenta the result would be red 3 blue 2 yellow 1 green 1 magenta 1 Sorry for my english and thanks to whoever can help me with this. =COUNTIF(A:A,"red")+COUNTIF(B:B,"red") etc. -- HTH Bo...

Exporting Date Modified Field
I'm trying to export Outlook 2003 Contacts to a .csv file. I want to include the MODIFIED field, which is a data field automatically stamped by Outlook. (It shows up on the FREQUENTLY USED FIELDS tab when displaying ALL FIELDS on a contact.) It doesn't come up as one of the mapped field choices when the wizard asks you to map fields to export. Anyone know how to export this field? If not, anyone know how to filter an export to include only those contacts whose MODIFIED field falls within a certain date range? TIA, Kathy Right now you can use our preview edition of ContactGen...

Calculating Days in Excel 97
Can someone tell me how to project a date in the future given a specific number of days? eg: Today is July 29, 2003, what date will it be 199 days from today's date. Thanks how about =today()+129 -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "dluhyt" <dluhyt@yahoo.com> wrote in message news:00bb01c35601$0df0d970$a601280a@phx.gbl... > Can someone tell me how to project a date in the future > given a specific number of days? eg: Today is July 29, > 2003, what date will it be 199 days from today's date. > > Thanks With 29-Jul-0...

Return comination of Current record and next record fields
I have a table: Run_point_List_ID 1 (Autonumber) Run_point_Venue_A Run_point_Venue_B Run_point_Venue_C Run_point_Venue_D How can I get the query to return the first two fields from the 1st record and the second two fields from the 2nd record, and so on... like the example below? From Record 1 From Record 1 From Record 2 From Record 2 From Record 2 From Record 2 From Record 3 From Record 3 From Record 3 From Record 3 From Record 4 From Record 4 From Record 4 From Record 4 From Record 5 From Record 5 From Record 5 From Record 5 From Record 6 From Record 6 ...

Date Difference 02-24-10
Good Morning. I am trying to figure out how to calculate the number of days between two dates. I have a table callled events. That table has event date, event type, event outcome. For example a defendant will have an Advisement Date (AA as stored in the table) and say a Preliminary Hearing date (PH). When i enter information I will enter the date, event type and the event outcome will be (PD for Pending, CN for Continued, or CP for completed). For example Case Number 10-12345 will have an AA on 01/05/10 which is Completed and a Preliminary Hearing on 02/05/10 which was C...

Pivot Table
Hi, I have a Pivot table with some numbers in it. Is there a way to replace the numbers with a text? For example: replace all numbers >1 with "yes" . Is this possible? If not whats the best way to achieve this? Regards, Vinstream Only by changing your data source. Eg. Sub Test() Dim rCell As Range For Each rCell In Range("DataSource").Columns _ (2).SpecialCells(xlCellTypeConstants, xlNumbers).Cells If rCell > 1 Then rCell = "yes" Next rCell End Sub "vinstream" <vinstream@gmail.com> wrote in mes...

Different Account Balance
I have a different account balance when I view all transactions vs. view transactions by past 12 months. My account balance from balance account is also different from my account balance vs. all transactions. I have read the KB article at: http://support.microsoft.com/default.aspx?scid=kb;en-us;838713&Product=mny I exported then deleted all my existing transactios and re-imported them (method 3), but all my account balances are wrong. I don't want to use this file since it is almost twice the size of my original file. I know there are 7 transactions that are wrong (the remaining t...

Pivot Table Solve Order
I'm trying to change the solve order of formulas I've created inside a pivot table. However, when I go to: Formulas -> Solve Order in the pivot menu, the Calculated Item Solve Order dialog box is empty. I've used the Formulas -> List Order to produce the list. So, I can see the current order. But, I'd like to change the order. How is this accomplished? Thanks!!! Hi The Solve Order refers to Calculated Items, not Calculated Fields. I suspect that you have Calculated Fields in your PT, but you do not have any Calculated Items -- ...

save a document where fields be used but not altered
I have careated a documents that need to be saved but not as read only. The feilds are needed, such as a form. How can I save this material so the format remains but text box alterations and movement areno longer on the page. There isn't a surefire way of locking the form, but you might want to try this as a workaround. Once the form is complete, group all elements together. Then reduce the viewing size down so the entire pasteboard area is visible on your monitor. Draw a single line from the top of the pasteboard area to the bottom and then a second line from side to side. Move t...

re:Time Differences
This is a multi-part message in MIME format. ------=_NextPart_000_0021_01C4D2DA.06832CE0 Content-Type: multipart/alternative; boundary="----=_NextPart_001_0022_01C4D2DA.06832CE0" ------=_NextPart_001_0022_01C4D2DA.06832CE0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a spread sheet where I am looking at what we call turnover times = and case length and I need to have difference in times sometimes be a = sometimesnegative and a positive but it won't allow a negative number to = display as hours and minutes any th...

calculating in a changing range
I am trying to find a maximum value in a range that will be changing (in starting position, and length). Here is an example, this is cells A1 to A13: 2 1 0 1 2 3 4 3 2 1 0 1 2 I want to find the max between zero points (In this case, there would be one max, and it would be 4). The number of zero points and the number of cells between them will change. Anyone have some idea of how to go about this, or anything to even get me started? Thank you! -- gkaste ------------------------------------------------------------------------ gkaste's Profile: http://www.excelforum.com/member.php?...

VB code based on different forms loaded
I have a Database that i use for taking ordes, the form is called frmOrder I have now created a very simply vertions of frmOrder called frmOrderTill that is will be used in our shop ( does same stuff but very basic Hoever some of the subform replating to frmOrder and now frmORderTill, have code in it that refers back to frmOrder, is there a way to write the code so it can use either frmOrder or frmOrderTill depending on what form is open Example of code used Forms![frmOrderProductAdd]![frmOrderProductTopBar].Form![OrderNumber] = Forms![frmOrder]![OrderNumber] i need a way t...

Insert Into query statement error between tables within same DB,Up
Use following in a query to run, get syntax error for Insert Into statement: INSERT INTO Kopia av Kontakter [(Efternamn[, Förnamn[, Företag[, Kategori[, Språk[, E-postadress[, Telefon-arbete[, Telefon2[, Faxnummer[, Mobiltelefon[, Telefon-hem[, Godk epost reklam]]]]]]]]]]])] SELECT [Reinholds-Kontakter-Test].Efternamn, [Reinholds-Kontakter-Test].Förnamn, [Reinholds-Kontakter-Test].Företag, [Reinholds-Kontakter-Test].Kategori, [Reinholds-Kontakter-Test].Språk, [Reinholds-Kontakter-Test].[E-postadress], [Reinholds-Kontakter-Test].[Telefon-arbete], ...

How do I calculate total of months that have passed?
I want to know how many months have passed since a certain date. There is a start date and an end date. I want to calculate how many months are in that period. For instance if the start date is 05-01-01 and the end date is 05-03-01 two months have passed. Hi, You can use =DATEDIF(A1,B1,"m") where, A1 = 05-01-01 B1 = 05-03-01 "m" = month ("d" for days, "y" for Years, "ym" for Months Excluding Year, "yd" for Days Excluding Years and "md" for Days Excluding Months And Years) Regards Akhilesh Dalia akhileshdalia<at&g...

Item Description field in Requisition Management
I would like the Item Description field added to the Requisition Management module for the business portal. I asked for a customization and was told by Microsoft Support that it was not possible. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.co...

Address State Field
I have a client that has a need to make the address state field a drop down so that they can run reports off of it. Is there any way to do this? The only way I thought of was by creating a new field with a drop down. Any ideas would be appreciated..... Thanks! -- Tara Sifuentes, CCDA, CCNA Hi, We always create a new drop down field for Stateorprovince and we write a script in "onchange" on the properties --> Event of this new field. The code update the original field and you don't need to change all of your reports. the only thing is you need to keep the original f...

Web Query How do I change columns of the MSN Stock quotes table?
I want to add the opening price to the web query MSN Moneycentral Investor Stock Quotes. How can I adjust which columns are included in this table If you are talking about the MSN Money Stock Quotes that appears on the data menu, if you select that option, then Help, you'll get to a web page that has another link entitled MSN Stock Quote Function Reference (or something similar). It lists the information that's available and explains the syntax of the formulas. On Wed, 20 Oct 2004 06:13:07 -0700, "BKM24" <BKM24@discussions.microsoft.com> wrote: >I want to add the...

turn automatic calculation off forever!
I want to turn off automatic calculation for all new Excel sessions I open. When I change the settings and close Exeel the changes are lost in a new session. I tried to save a sheet called book1.xls in my xlstartup folder with calculations set to manual. Close Excel and open a new Excel sesson remembers the change. When I open one more Excel sesson it says that the file book1.xls is locked for editing. Any ideas? I am using Excel 2003 and are sometimes using Excel 2000 as well. Regards Lars Schouw I'd open excel and hide book1.xls (window|hide) Then close excel and answer yes to th...

using the same CRM connection to add tables
Hello, I'm creating reports in CRM using the microsoft CRM connection to get the data. In a previous workout I added some sights and want now to add some further sights. Now the problem: How can add tables or sights using the same connection I used in previous workout. If I add new tables, sights a new connection (*SSO*) is added and this is more than worst to the report speed. Thanks for your help Thomas Ott (itvt germany) ...

CRM+GP HOW TO LOCK A FIELD
is there a way to lock a field so a salesman cant edit and overide a parts discription Brent In Great Plains, a customisation like this can be easily achieved with a variety of tools. You can use Modifier, VBA, Dexterity or each the Field Level Security module. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This posting is provided "A...

Customizing Pivot Table Totals
When i set the options to Total a row I get totals for all 10 of my data fields, which would be fine, except 3 of these data fields should not be included in the display of totals. Is there some settings that I can configure so that certain data fields are not inlcluded in the totals? Below is an example City Project Type Data Jan Feb 1 a Houses 4 5 Cost 3 6 People 12 3 b Houses 5 11 Cost 4 10 ...