How to update a field with the value from another table?

Hi,

I would like to know how to update a field with the value
from another field from another table?
For example:
I have Table1, StatusField.
and Table2, StatusField.
Let's say the Status field in Table2 can have values such
as "Completed", "In progress" or "Open"
I would like these values to be able to appear
automatically in Table1, StatusField.

I am also pretty new with Vb, so any help would be
appreciated.
Thanks

On Tue, 11 May 2004 06:51:55 -0700, "Chris"
wrote:

Hi,

I would like to know how to update a field with the value
from another field from another table?
For example:
I have Table1, StatusField.
and Table2, StatusField.
Let's say the Status field in Table2 can have values such
as "Completed", "In progress" or "Open"
I would like these values to be able to appear
automatically in Table1, StatusField.

I am also pretty new with Vb, so any help would be
appreciated.
Thanks

No VB is needed or appropriate in this case.

In fact, I'd say that the need to do this operation AT ALL is very
much suspect. It sounds like you're trying to store the Status field
redundantly in two tables - this will *always* leave you vulnerable to
update anomalies, such as the status being "Open" in one table and
"Complete" in the other! Can you not create a Query joining the two
tables to look up the current status?

If you do need to do this - and I'd appreciate an explanation of *why*
you would need to do so - the appropriate tool would be an Update
Query. Create a Query joining the two tables (you will need a Primary
Key as the joining field in the table you're trying to update), change
it to an Update query using the query type icon, and update
Table1.StatusField to

[Table2].[StatusField]


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Thank you for your reply,

I will try the query.
Cheers,
Chris.
-----Original Message-----
On Tue, 11 May 2004 06:51:55 -0700, "Chris"
wrote:

Hi,

I would like to know how to update a field with the
value
from another field from another table?
For example:
I have Table1, StatusField.
and Table2, StatusField.
Let's say the Status field in Table2 can have values
such
as "Completed", "In progress" or "Open"
I would like these values to be able to appear
automatically in Table1, StatusField.

I am also pretty new with Vb, so any help would be
appreciated.
Thanks

No VB is needed or appropriate in this case.

In fact, I'd say that the need to do this operation AT
ALL is very
much suspect. It sounds like you're trying to store the
Status field
redundantly in two tables - this will *always* leave you
vulnerable to
update anomalies, such as the status being "Open" in one
table and
"Complete" in the other! Can you not create a Query
joining the two
tables to look up the current status?

If you do need to do this - and I'd appreciate an
explanation of *why*
you would need to do so - the appropriate tool would be
an Update
Query. Create a Query joining the two tables (you will
need a Primary
Key as the joining field in the table you're trying to
update), change
it to an Update query using the query type icon, and
update
Table1.StatusField to

[Table2].[StatusField]


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
.