update statement

[Dagney Taggart]

I have a simple question. I am working on a project and I need to update
information from one table to another. I have written the sql statement
that compares the two tables. I am grabbing anything that is similar from
both tables. What I want to do?

I want to grab the updated information from the second (updated table) and
update (replace) the information in the first table (this also includes the
matching field and several others). How does one do this within the
confines of access 2000?

Thanks-in-advance

 

[John Vinson]

I have a simple question. I am working on a project and I need to update
information from one table to another. I have written the sql statement
that compares the two tables. I am grabbing anything that is similar from
both tables. What I want to do?

I want to grab the updated information from the second (updated table) and
update (replace) the information in the first table (this also includes the
matching field and several others). How does one do this within the
confines of access 2000?

Thanks-in-advance

You must have a join on a field which uniquely identifies which record
is to be updated. You should be able to create a Query joining the two
tables; change it to an Update query using the Query menu option or
the query type icon in the toolbar. On the new "Update To" row you can
type

[Tablename].[Fieldname]

specifying the field which you wish to use as the source of the new
data, and run the query by clicking the ! icon.

MAKE A BACKUP of the database first - updates aren't reversible.

One thing to consider: generally, it's a Very Bad Idea to have the
same data stored redundantly in two tables. Are you *quite* certain
that it is necessary to do this? What are the two tables, and what
fields are you updating?

John W. Vinson[MVP]

 

[Dagney Taggart]

I have a simple question. I am working on a project and I need to update
information from one table to another. I have written the sql statement
that compares the two tables. I am grabbing anything that is similar from
both tables. What I want to do?

I want to grab the updated information from the second (updated table) and
update (replace) the information in the first table (this also includes the
matching field and several others). How does one do this within the
confines of access 2000?

Thanks-in-advance

You must have a join on a field which uniquely identifies which record
is to be updated. You should be able to create a Query joining the two
tables; change it to an Update query using the Query menu option or
the query type icon in the toolbar. On the new "Update To" row you can
type

[Tablename].[Fieldname]

specifying the field which you wish to use as the source of the new
data, and run the query by clicking the ! icon.

MAKE A BACKUP of the database first - updates aren't reversible.

One thing to consider: generally, it's a Very Bad Idea to have the
same data stored redundantly in two tables. Are you *quite* certain
that it is necessary to do this? What are the two tables, and what
fields are you updating?

John W. Vinson[MVP]

Hey Senior Vinson,

I hate using MS Query...so I usually just type what I want done. Anyway, I
already did the "join" in the first query to see what records were in the
second table that I could use to update the first table. Now I just want to
use that particular identifier to update other fields of information that I
don't have.... So I am going to use that join to update the first table
with information that is in the second table. I do realize that I don't
need to update the unique identifier. Do I need to use an if statement?

Thanks

 

[Dagney Taggart]

For what i am trying to do, I don't think it can be done through access
query. The best way is to write a function (module) to do the select and
then update for me. Access through query doesn't have the capability to do
this. If it is possible, please let me know.

Just to revist what I am trying to do: pull information from two tables
with the same indentifier and then update the first table's corresponding
fields that are empty with additional information from the second table. [I
hope this makes sense.] I am working with thousands of records so to do this
manually would be tedious.

If anyone has any suggestions, please let me know.

Thanks-in-advance

I have a simple question. I am working on a project and I need to update
information from one table to another. I have written the sql statement
that compares the two tables. I am grabbing anything that is similar from
both tables. What I want to do?

I want to grab the updated information from the second (updated table) and
update (replace) the information in the first table (this also includes the
matching field and several others). How does one do this within the
confines of access 2000?

Thanks-in-advance

You must have a join on a field which uniquely identifies which record
is to be updated. You should be able to create a Query joining the two
tables; change it to an Update query using the Query menu option or
the query type icon in the toolbar. On the new "Update To" row you can
type

[Tablename].[Fieldname]

specifying the field which you wish to use as the source of the new
data, and run the query by clicking the ! icon.

MAKE A BACKUP of the database first - updates aren't reversible.

One thing to consider: generally, it's a Very Bad Idea to have the
same data stored redundantly in two tables. Are you *quite* certain
that it is necessary to do this? What are the two tables, and what
fields are you updating?

John W. Vinson[MVP]

Hey Senior Vinson,

I hate using MS Query...so I usually just type what I want done. Anyway, I
already did the "join" in the first query to see what records were in the
second table that I could use to update the first table. Now I just want to
use that particular identifier to update other fields of information that I
don't have.... So I am going to use that join to update the first table
with information that is in the second table. I do realize that I don't
need to update the unique identifier. Do I need to use an if statement?

Thanks

 

[Rick Brandt]

For what i am trying to do, I don't think it can be done through
access query. The best way is to write a function (module) to do the
select and then update for me. Access through query doesn't have
the capability to do this. If it is possible, please let me know.

Just to revist what I am trying to do: pull information from two
tables with the same indentifier and then update the first table's
corresponding fields that are empty with additional information from
the second table. [I hope this makes sense.] I am working with
thousands of records so to do this manually would be tedious.

If anyone has any suggestions, please let me know.

What have you tried? Access is perfectly capable of doing this. In the
"Update To" field under a field from Table1 you simply put
[Table2]![FieldName] and Access will take the value from Table2 and insert
it into the field in Table1.

 

[John Vinson]

On Tue, 8 Mar 2005 07:23:03 -0800, "Dagney Taggart" <Dagney

For what i am trying to do, I don't think it can be done through access
query. The best way is to write a function (module) to do the select and
then update for me. Access through query doesn't have the capability to do
this. If it is possible, please let me know.

Access is perfectly capable of doing this; in fact it's completely
routine. No VBA code is needed whatsoever.

Obviously you did not try what I suggested. I'll restate it in case I
was unclear.

Just to revist what I am trying to do: pull information from two tables
with the same indentifier and then update the first table's corresponding
fields that are empty with additional information from the second table. [I
hope this makes sense.] I am working with thousands of records so to do this
manually would be tedious.

In the Access Query design window (I'm not talking about Microsoft
Query here, this is Access 2000) create a new Query (or use the join
query that you now have).

Join the two tables by ID. Let's say you have TableA and TableB, and
that you want to update TableA's NULL fieldX with the value in FieldX
from TableB.

Change the query to an Update query using the Query menu item. Update
is one of the options.

Put a criterion on TableA.FieldX of

IS NULL

to prevent overwriting existing data.

On the UpdateTo line put

[TableB].[FieldX]

Run the query. It will do what you ask.


If you have multiple fields, some of which will be null and some
won't, and you can't predict which fields might be null, a slightly
different query may be necessary; leave off the IS NULL criterion,
and on the UpdateTo line under TableA.FieldX put

NZ([TableA].[FieldX], [TableB].[FieldX])

This will update FieldX to itself (a time-consuming do-nothing
operation, which is why I didn't recommend it on the first pass) if it
exists, and update it to the value from TableB if (in this record)
it's empty.

John W. Vinson[MVP]

 

[Dagney Taggart]

Just wanted to thank both of you for the help. It worked!

On Tue, 8 Mar 2005 07:23:03 -0800, "Dagney Taggart" <Dagney

For what i am trying to do, I don't think it can be done through access
query. The best way is to write a function (module) to do the select and
then update for me. Access through query doesn't have the capability to do
this. If it is possible, please let me know.

Access is perfectly capable of doing this; in fact it's completely
routine. No VBA code is needed whatsoever.

Obviously you did not try what I suggested. I'll restate it in case I
was unclear.

Just to revist what I am trying to do: pull information from two tables
with the same indentifier and then update the first table's corresponding
fields that are empty with additional information from the second table. [I
hope this makes sense.] I am working with thousands of records so to do this
manually would be tedious.

In the Access Query design window (I'm not talking about Microsoft
Query here, this is Access 2000) create a new Query (or use the join
query that you now have).

Join the two tables by ID. Let's say you have TableA and TableB, and
that you want to update TableA's NULL fieldX with the value in FieldX
from TableB.

Change the query to an Update query using the Query menu item. Update
is one of the options.

Put a criterion on TableA.FieldX of

IS NULL

to prevent overwriting existing data.

On the UpdateTo line put

[TableB].[FieldX]

Run the query. It will do what you ask.


If you have multiple fields, some of which will be null and some
won't, and you can't predict which fields might be null, a slightly
different query may be necessary; leave off the IS NULL criterion,
and on the UpdateTo line under TableA.FieldX put

NZ([TableA].[FieldX], [TableB].[FieldX])

This will update FieldX to itself (a time-consuming do-nothing
operation, which is why I didn't recommend it on the first pass) if it
exists, and update it to the value from TableB if (in this record)
it's empty.

John W. Vinson[MVP]