update field from one table with field from another

[Elise]

I want to update a table with the data of another. Is there some way to pull
data from one to the other. Each table has a common field. Where would I
start?

 

[Micah Chaney]

Utilize the Update Query. If you don't have that available, ensure that all
your Access features are installed. If you need more specific help, provide
version of Access and some examples/fields of data your trying to update.

 

[John Vinson]

I want to update a table with the data of another. Is there some way to pull
data from one to the other. Each table has a common field. Where would I
start?

Back up your database first, just in case!

Create a Query joining the two tables on the common field. (It must
have a unique Index in the "one" side table for this to work, such as
a Primary Key).

Apply whatever criteria you need to limit the updates to those records
you actually want updated.

Change the query to an Update query using the query type icon or the
Query menu option.

On the Update To line under each field that you want to update, type

[OtherTableName].[FieldName]

using your own table and field names of course. The brackets are
essential, or it will update the table to "OtherTableName.FieldName"
rather than the contents of those fields!

Run the query by clicking the ! icon and check the results.

John W. Vinson[MVP]

 

[Elise]

I am using Access in the Office XP professional version 2002. I have two
identical tables with the same data. I have a copy on my laptop and my
coworker has a copy on theirs. We are in a situation where we cannot share
the same table because we are working in remote areas without access to a
network. I update certain records and my coworking updates others. I am
trying to find a way to merge their updates with mine in a main table leaving
the records that haven't been updated. Then I want to purge their table of
all the updated records so that they can work with a smaller table. The
fields are :

address
name
numberofchildren (this is actually a series of fields that break out the
number of children by their age)
phone
updateby

The address field is completed and there are A LOT of records. The rest of
the fields are the ones being updated.

I don't see where I have an update query option. I have upgraded my version
of access with an upgrade sent by Microsoft. I have the option to create a
query but I haven't been able to do anything more that display fields. I
have't even seen an option that would allow me to use a selection criterion.

 

[Micah Chaney]

ACTUALLY -- You may wanna think about how you're doing this. If you and your
coworker are both updating different records in the same table, and they
aren't linked, you have a problem. Say you have records A, B, C, D, and E.
You update records A and B. And you apply your updates to this table. Now
your coworker updates records C and D, well unless you have some sort of
filter to only update records C and D, then your coworker is going to update
records A and B back to their original value, because that's the value she
has for A and B. More to follow.

I want to update a table with the data of another. Is there some way to pull
data from one to the other. Each table has a common field. Where would I
start?

Back up your database first, just in case!

Create a Query joining the two tables on the common field. (It must
have a unique Index in the "one" side table for this to work, such as
a Primary Key).

Apply whatever criteria you need to limit the updates to those records
you actually want updated.

Change the query to an Update query using the query type icon or the
Query menu option.

On the Update To line under each field that you want to update, type

[OtherTableName].[FieldName]

using your own table and field names of course. The brackets are
essential, or it will update the table to "OtherTableName.FieldName"
rather than the contents of those fields!

Run the query by clicking the ! icon and check the results.

John W. Vinson[MVP]

 

[Micah Chaney]

Sorry for the double post, I just wanted you to see that before you did
anything. What I suggest is this:
It seems you have 3 tables. WorkerA (You), WorkerB (Your coworker, and C
(The table you both update. You and your coworker should create a new field,
with a value of "Updated" or something, so that you can filter based on the
value of this field only those records you want to update. Then you would
follow Mr. Vinson's instructions on how to actually perform the update. Does
this help?

I want to update a table with the data of another. Is there some way to pull
data from one to the other. Each table has a common field. Where would I
start?

Back up your database first, just in case!

Create a Query joining the two tables on the common field. (It must
have a unique Index in the "one" side table for this to work, such as
a Primary Key).

Apply whatever criteria you need to limit the updates to those records
you actually want updated.

Change the query to an Update query using the query type icon or the
Query menu option.

On the Update To line under each field that you want to update, type

[OtherTableName].[FieldName]

using your own table and field names of course. The brackets are
essential, or it will update the table to "OtherTableName.FieldName"
rather than the contents of those fields!

Run the query by clicking the ! icon and check the results.

John W. Vinson[MVP]

 

[Elise]

Thank you very much. It worked out perfectly!

I want to update a table with the data of another. Is there some way to pull
data from one to the other. Each table has a common field. Where would I
start?

Back up your database first, just in case!

Create a Query joining the two tables on the common field. (It must
have a unique Index in the "one" side table for this to work, such as
a Primary Key).

Apply whatever criteria you need to limit the updates to those records
you actually want updated.

Change the query to an Update query using the query type icon or the
Query menu option.

On the Update To line under each field that you want to update, type

[OtherTableName].[FieldName]

using your own table and field names of course. The brackets are
essential, or it will update the table to "OtherTableName.FieldName"
rather than the contents of those fields!

Run the query by clicking the ! icon and check the results.

John W. Vinson[MVP]

 

[John Vinson]

Thank you very much. It worked out perfectly!

On reading your followup to Micah - he's right; my suggestion may be
too much a blunt instrument.

The technology designed for your situation is called "Replication".
It's rather complicated but works effectively for just this purpose.
Do a search on support.microsoft.com for the term, download the
Replication Whitepaper, and *read it carefully*. It's best to use the
Database Splitter (even if each of you will be using the database
solo) and replicate only the backend; Forms and Reports don't take
well to replication.

John W. Vinson[MVP]

 

[PC Pete]

Create a Query joining the two tables on the common field. (It must
have a unique Index in the "one" side table for this to work, such as
a Primary Key).

John, thanks for that clear info.

I have one comment: I had a similar issue where I wanted to update one field
in one table based on non-indexed fields in another table, and I couldn't
figure out why when I tried using the query builder it kept on failing to
update any rows (0 rows updated), despite the field joins and data being
correct.
It wasn't until I opened the query in SQL view that I saw that the query
builder had inserted a where clause matching the (completely irrelevant)
primary keys. When I removed that clause, the update worked perfectly.

This is a confusing area for MDB novices like me, and I spent more than 3
hours trying to apply my SQL knowledge to the Access query mechanism, without
much success.

Once I figured out what Access was trying to do "behind my back", it all
worked out OK.

But thanks for the description, it really helped.

 

[John W. Vinson]

I have one comment: I had a similar issue where I wanted to update one field
in one table based on non-indexed fields in another table, and I couldn't
figure out why when I tried using the query builder it kept on failing to
update any rows (0 rows updated), despite the field joins and data being
correct.
It wasn't until I opened the query in SQL view that I saw that the query
builder had inserted a where clause matching the (completely irrelevant)
primary keys. When I removed that clause, the update worked perfectly.

All I can think of is that if you have a relationship defined in the
relationships window, a Query will default to joining by those fields. You
must explicitly delete the join lines to force a different join.

Why it didn't show the join lines in the query builder I can't guess!

John W. Vinson [MVP]