A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

One last try



 
 
Thread Tools Display Modes
  #1  
Old May 28th, 2010, 04:03 PM posted to microsoft.public.access.gettingstarted
HubbyMax
external usenet poster
 
Posts: 35
Default One last try

Thought I would give it one more try. How do I run a calculation that uses a
field from 2 different tables?

Table A - field, current
Table 2 - field, out

current - out = ? to be placed in 'total' field in Table A
  #2  
Old May 28th, 2010, 04:40 PM posted to microsoft.public.access.gettingstarted
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default One last try

You should not store calculated data as it will be in error if records are
revised. Whenever the calculation results are needed it should be done then
in a query.

First you have got to have a field that has common data in both tables so
you know which record from table A to use with record from table B.

BACKUP DATABASE BACKUP DATABASE BACKUP DATABASE

Then in query design view put both tables in the space above the grid.

Click on the common data field in table A and drag to the common data field
in table B. This will create a connecting line.

In the FIELD row of the grid type this --
My_Total: [Table A].[current] + [Table 2].[out]

If there may be any null fields then use this --
My_Total: Nz([Table A].[current], 0) + Nz([Table 2].[out], 0)

Click on the 'Total' field of Table A and drag to the FIELD row. Click on
query type and select Update Query. Copy the formula only (all after the
colon) into the Update To: row of the grid below 'Total' in the grid.

Run the query.

--
Build a little, test a little.


"HubbyMax" wrote:

Thought I would give it one more try. How do I run a calculation that uses a
field from 2 different tables?

Table A - field, current
Table 2 - field, out

current - out = ? to be placed in 'total' field in Table A

  #3  
Old May 28th, 2010, 04:49 PM posted to microsoft.public.access.gettingstarted
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default One last try

"One more" suggests previous attempts. What happened? If you posted a
question in a newsgroup and were told you should not store the calculation
result, you will be disappointed that I am saying the same thing.

As for performing the calculation, whether or not it is stored, there is not
enough information on which to base a suggestion. It seems there are two
tables where there should be one. Please describe a little more about the
table structure. If proper design indicates two tables, there must be a way
to associate a record in Table A with a record in Table 2. What is the
connection between the records in the two tables?

HubbyMax wrote:
Thought I would give it one more try. How do I run a calculation that uses a
field from 2 different tables?

Table A - field, current
Table 2 - field, out

current - out = ? to be placed in 'total' field in Table A


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...arted/201005/1

  #4  
Old May 28th, 2010, 06:39 PM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default One last try

On Fri, 28 May 2010 08:03:01 -0700, HubbyMax
wrote:

Thought I would give it one more try. How do I run a calculation that uses a
field from 2 different tables?

Table A - field, current
Table 2 - field, out

current - out = ? to be placed in 'total' field in Table A


Insufficient information.

How many records in Table A? How many records in Table 2? Which record in
Table A goes with which record in Table 2? Do you have any way of joining the
tables? Why do you want to store derived data in the Total field *at all*? If
the Total can be calculated from other fields, it should be calculated, not
stored.
--

John W. Vinson [MVP]

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:55 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.