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

[Chris]

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

 

[John Vinson]

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]

 

[Chris]

Thank you for your reply,

I will try the query.
Cheers,
Chris.

-----Original Message-----

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]



.