Update Query

I'm trying to setup a seemingly easy function but can't seem to find the
solution. I have two tables with the same structure except the 2nd table has
an extra field. How can I insert that field into the 1st table when a match
occurs between both tables?

Thanks J

J -

If the first table doesn't have the field in its structure, then you must
add the field to the table before you can update the data. Assuming both
tables have the same structure, and the second table has some data in a field
that is null in the first table, then you can use an update query like this:

UPDATE table1, table2
SET table1.fieldname = table2.fieldname
WHERE table1.fieldname is null
AND table2.primarykey is not null
AND table1.primarykey = table2.primarykey

You will of course need to use your real table and field names, making sure
to inner join on the field(s) that uniquely identify each record.
--
Daryl S


"jskay" wrote:

I'm trying to setup a seemingly easy function but can't seem to find the
solution. I have two tables with the same structure except the 2nd table has
an extra field. How can I insert that field into the 1st table when a match
occurs between both tables?

Thanks J

.

"jskay" u57998@uwe wrote in message news:a323b9a853cfa@uwe...
I'm trying to setup a seemingly easy function but can't seem to find the
solution. I have two tables with the same structure except the 2nd table
has
an extra field. How can I insert that field into the 1st table when a
match
occurs between both tables?


Do you really mean that the 2nd table has an extra field in its design?
That is:

Table1
---------
Field1
Field2
Field3

Table2
---------
Field1
Field2
Field3
Field4

?
Or do you mean that the tables have the same structure and number of fields,
but the second table has data in one field that the first table does not?

You can't selectively add a field to a table; the field exists for all
records or not at all. But you can update that field for some records. I
have to think that that is what you want to do. So for example, both tables
may have fields Field1, Field2, Field3, and Field4, and you may want to
update those records in Table1 that have the same value in Field1 as a
record in Table2, setting Field4 in Table1.

If that's what you're trying to do, the SQL of such a query would be like
this:

UPDATE Table1 INNER JOIN Table2
ON Table1.Field1 = Table2.Field1
SET Table1.Field4 = Table2.Field4


--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)