Update Query

I'm trying to move information from a table to a set of
normalized tables. To do this I'm trying to use an
append query.

The destination tables has one main table, 2 other tables
that have a 1 to many relationship with the main table,
and another table that is many to many.

I've found that if I set a query the joins the main table
with one of the 1-M tables the updates to those two
tables work. However, if I change the query (the target
of the append query) to include the main table and 2 1-M
tables, the query then becomes "not updatable".

I can write a program to do the work, but I'm just
wondering if there is a SQL query solution to the problem.

Thanks,
Leif

Do all tables involved have primary keys, including the 'many to many'
table? If not, that is probably the cause of your problem.



"Leif" schreef in
:

I'm trying to move information from a table to a set of
normalized tables. To do this I'm trying to use an
append query.

The destination tables has one main table, 2 other tables
that have a 1 to many relationship with the main table,
and another table that is many to many.

I've found that if I set a query the joins the main table
with one of the 1-M tables the updates to those two
tables work. However, if I change the query (the target
of the append query) to include the main table and 2 1-M
tables, the query then becomes "not updatable".

I can write a program to do the work, but I'm just
wondering if there is a SQL query solution to the problem.

Thanks,
Leif

-----Original Message-----
Do all tables involved have primary keys, including
the 'many to many'
table? If not, that is probably the cause of your
problem.


Yes, all tables have a primary key (PK). The main table
has a PK called ProjectNo. The two 1-M tables have a
composite PK using the parent key plus an additional
field (which is an autonumber field for uniqueness).

The Many to Many table has an intersect table that
contains the PK of the main table and the PK of the other
table.

Leif



"Leif" schreef in
:

I'm trying to move information from a table to a set
of
normalized tables. To do this I'm trying to use an
append query.

The destination tables has one main table, 2 other
tables
that have a 1 to many relationship with the main
table,
and another table that is many to many.

I've found that if I set a query the joins the main
table
with one of the 1-M tables the updates to those two
tables work. However, if I change the query (the
target
of the append query) to include the main table and 2 1-
M
tables, the query then becomes "not updatable".

I can write a program to do the work, but I'm just
wondering if there is a SQL query solution to the
problem.

Thanks,
Leif


.