One last try

  • Thread starter Thread starter HubbyMax
  • Start date Start date
H

HubbyMax

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
 
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.
 
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
Click to expand...

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.
 
Back
Top