Prev: Error Message
Next: Final Reminder - Microsoft Responds to the Evolution of Community
From: HubbyMax on 28 May 2010 11:03
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
From: KARL DEWEY on 28 May 2010 11:40
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
From: BruceM via AccessMonster.com on 28 May 2010 11:49
"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/Forums.aspx/access-gettingstarted/201005/1

From: John W. Vinson on 28 May 2010 13:39
On Fri, 28 May 2010 08:03:01 -0700, HubbyMax
<HubbyMax(a)discussions.microsoft.com> 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]

 | 
Pages: 1
Prev: Error Message
Next: Final Reminder - Microsoft Responds to the Evolution of Community