Update table using VBA

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to see a simple example of using a numeric fiels in one table to
update a numeric field in a second table. The equivalent of:

Table1.field = Table1.field + table2.field.

using either an update query or code.

Thanks for your help.
 
Well, as explained about once a day in this newsgroup, you don't store
calculated values in a table in most cases. When you need that value in the
future, you simply calculate it. Storing the calculated value is redundant.

Please search the groups before posting a new thread and you will often find
your answer.

Rick B
 
Thanks for the reply Rick. This isn't a calculated value, but rather an
update to an existing value. Think of it as Table 1 has the number of
members in a classroom, with the table 2 value the number of additions to the
class. What's needed is the new total members in the classroom. I've tried
searching but couldn't find anything on point. Can you show me a reference
to follow?

Thanks again.
 
Thanks for the reply Rick. This really isn't a calculated value, but rather
an update to the exiting value. Table 1 is a master file holding the value
of points accumulated over a period of time. Table 2 is the points resulting
from this period's activity. I need to update the points to date with the
additional points earned.

I tried searching the group but could not find anything on point. Could you
point out a reference for me?

Thanks again.
 
I would think you could use an update query.

You should be able to pull both tables to a query, then in the 'update'
field you should be able to put a calculated entry as you describe below.
I've never tried doing a circular update like this. If it does not work,
you could always add an additional temporary field to store the value, then
do a second update.

Sorry I can't be more specific, but have never tried this.

Good Luck.

Rick
 
Generic code.

UPDATE Table1 INNER JOIN Table2
ON Table1.SomeLinkField = Table2.SomeLinkField
SET Table1.Field1 = Nz(Table1.Field1,0) + Table2.Field
WHERE Table2.Field is Not Null
 
Back
Top