Update Query or Filter Table based on another Table

[turtle]

I have Two tables (Table1 and Table2). Both tables have a common field
called part number. Table 1 contains an extra field that i would like
to update table 2 to match if the part number matches. I created a
join between Table1 and Table 2 but couldn't update Table 2 since the
recordset was unupdateable.

What i would like:

Table 1
Part Number Field 2
A BC b
DEX U
DFDA 6

Table 2 Field 2
ABC is null

I would like Field2 in table 2 to put in the B or at least allow me to
manually put it in without having to look up every part number
manually. If i could get a list and then copy over Field 2 that would
be fine.

KO

[Bob Quintal]

"turtle" <ko****@vistaco ntrols.comwrote in
news:11******** **************@ 74g2000cwt.goog legroups.com:

I have Two tables (Table1 and Table2). Both tables have a
common field called part number. Table 1 contains an extra
field that i would like to update table 2 to match if the part
number matches. I created a join between Table1 and Table 2
but couldn't update Table 2 since the recordset was
unupdateable.

What i would like:

Table 1
Part Number Field 2
A BC b
DEX U
DFDA 6

Table 2 Field 2
ABC is null

I would like Field2 in table 2 to put in the B or at least
allow me to manually put it in without having to look up every
part number manually. If i could get a list and then copy
over Field 2 that would be fine.

KO

A simple query joined on one field should be updateable.
join on the part number. set criterai for table1.field2 "is not
null" without the quotes. set the criteria for table2.field2 as
"IS Null" without the quotes. Run the select query and see if
you can add a new record (the >* at the bottom is not greyed
out). If it is greyed out, you have some sort of problem with
the primary keys of your table.

If you can add a row, you can convert to an update query. You
could also just click in the titlebar of table1.column2 to
select all the rows, and {ctrl-C} (copy) then select tho other
column2 and paste {Ctrl-V}.

HTH
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[turtle]

Bob,
thanks for the help. I have tried what you suggested but the query is
not updateable. The >* is greyed out. What kind of problem might i
have with my primary keys?

thanks for your time,
KO
Bob Quintal wrote:

"turtle" <ko****@vistaco ntrols.comwrote in
news:11******** **************@ 74g2000cwt.goog legroups.com:

I have Two tables (Table1 and Table2). Both tables have a
common field called part number. Table 1 contains an extra
field that i would like to update table 2 to match if the part
number matches. I created a join between Table1 and Table 2
but couldn't update Table 2 since the recordset was
unupdateable.

What i would like:

Table 1
Part Number Field 2
A BC b
DEX U
DFDA 6

Table 2 Field 2
ABC is null

I would like Field2 in table 2 to put in the B or at least
allow me to manually put it in without having to look up every
part number manually. If i could get a list and then copy
over Field 2 that would be fine.

KO

A simple query joined on one field should be updateable.
join on the part number. set criterai for table1.field2 "is not
null" without the quotes. set the criteria for table2.field2 as
"IS Null" without the quotes. Run the select query and see if
you can add a new record (the >* at the bottom is not greyed
out). If it is greyed out, you have some sort of problem with
the primary keys of your table.

If you can add a row, you can convert to an update query. You
could also just click in the titlebar of table1.column2 to
select all the rows, and {ctrl-C} (copy) then select tho other
column2 and paste {Ctrl-V}.

HTH
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"turtle" <ko****@vistaco ntrols.comwrote in
news:11******** **************@ i42g2000cwa.goo glegroups.com:

Bob,
thanks for the help. I have tried what you suggested but the
query is not updateable. The >* is greyed out. What kind of
problem might i have with my primary keys?

thanks for your time,
KO

What is the primary key in each of the two tables?
if there is no primary key, or the PK from the table you wish to
update in the query isn't in the query, this could cause the
query to be read-only.
Are the tables local or in a linked database?

In the query design mode check the Recordset Type property,
You might try setting this property to Dynaset (inconsistent
updates) instead of Dynaset.

Make sure that the unique values and unique records properties
are set to no.

Bob Quintal wrote:

>"turtle" <ko****@vistaco ntrols.comwrote in
news:11******* *************** @74g2000cwt.goo glegroups.com:

A simple query joined on one field should be updateable.
join on the part number. set criterai for table1.field2 "is
not null" without the quotes. set the criteria for
table2.field 2 as "IS Null" without the quotes. Run the select
query and see if you can add a new record (the >* at the
bottom is not greyed out). If it is greyed out, you have some
sort of problem with the primary keys of your table.

If you can add a row, you can convert to an update query. You
could also just click in the titlebar of table1.column2 to
select all the rows, and {ctrl-C} (copy) then select tho
other column2 and paste {Ctrl-V}.

HTH
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com