Need help with Query results

[Guest]

I have 2 tbls, The first tabl (TblOblig) has one ObligID number for
multilple Product ID #'s in tbl 2 (ProductTbl). I want to set up a query
(UPDATE) when a fieled has changed which could be more than one in the Oblig
Tbl, that it only changes the correct field(s) for which that paticular
Product number is associated with in the Oblig table. Because one Unique
ObligID number could be associated to more than one product ID number, I want
the change to reflect the correct ProductID number and not make the change to
all Product #'s and thier fields associated with that Oblig Id#. This is an
update query. Here is my SQL for the query but it reflects a change to all
product numbers associated with the correct Oblig number and not just the one
that it is associated with. PLEASE HELP!

UPDATE dbo_tblObligationType INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID SET
DEV_dbo_TblProduct.ProductId = dbo_tblproduct.productid,
DEV_dbo_TblProduct.BondType = dbo_tblObligationType!BondType,
DEV_dbo_TblProduct.CategoryId = dbo_tblObligationType!CategoryID,
DEV_dbo_TblProduct.ProductLine = dbo_tblObligationType!ProductLine
WHERE (((DEV_dbo_TblProduct.ProductId)=[DBO_TBLPRODUCT].[PRODUCTID]) AND
((DEV_dbo_TblProduct.BondType)<>[dbo_tblObligationType].[BondType])) OR
(((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine])) OR
(((DEV_dbo_TblProduct.CategoryId)<>[dbo_tblObligationType].[CategoryID]));

 

[Guest]

If you are talking about changing the primary key field in TblOblig and
wanting to have the foriegn key field automatically updated in the
ProductTbl, Access will do it for you. In the Relationship window create a
relationship between these two tables. Then turn of Referiential Integrity
and try to save it. If you have dirty data, you'll need to fix it. Once you
get RI working, turn on the Cascade Update option. Then if you change the PK,
the FK in related tables will be automatically updated.

If, on the other hand, you are talking about changing data in one table
based on data in another that is not part of the relationship, you need to
rethink your design.

 

[Guest]

Yes thank you. Part two of you answer is correct. This design does need to be
rethought but I am not the author nor would I ever claim to be. The company
is cheap and wants the original queries to work. Nothing more and nothing
less. That is why I need to find a solution. I ahve done it on another query
but this one is a little different making it more of a challange. because it
is linked and set though SQL no properties and or tables can and will not be
modified. To top that off I am working with live data. 2 month project and
80% done! Cant wait!

If you are talking about changing the primary key field in TblOblig and
wanting to have the foriegn key field automatically updated in the
ProductTbl, Access will do it for you. In the Relationship window create a
relationship between these two tables. Then turn of Referiential Integrity
and try to save it. If you have dirty data, you'll need to fix it. Once you
get RI working, turn on the Cascade Update option. Then if you change the PK,
the FK in related tables will be automatically updated.

If, on the other hand, you are talking about changing data in one table
based on data in another that is not part of the relationship, you need to
rethink your design.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have 2 tbls, The first tabl (TblOblig) has one ObligID number for
multilple Product ID #'s in tbl 2 (ProductTbl). I want to set up a query
(UPDATE) when a fieled has changed which could be more than one in the Oblig
Tbl, that it only changes the correct field(s) for which that paticular
Product number is associated with in the Oblig table. Because one Unique
ObligID number could be associated to more than one product ID number, I want
the change to reflect the correct ProductID number and not make the change to
all Product #'s and thier fields associated with that Oblig Id#. This is an
update query. Here is my SQL for the query but it reflects a change to all
product numbers associated with the correct Oblig number and not just the one
that it is associated with. PLEASE HELP!

UPDATE dbo_tblObligationType INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID SET
DEV_dbo_TblProduct.ProductId = dbo_tblproduct.productid,
DEV_dbo_TblProduct.BondType = dbo_tblObligationType!BondType,
DEV_dbo_TblProduct.CategoryId = dbo_tblObligationType!CategoryID,
DEV_dbo_TblProduct.ProductLine = dbo_tblObligationType!ProductLine
WHERE (((DEV_dbo_TblProduct.ProductId)=[DBO_TBLPRODUCT].[PRODUCTID]) AND
((DEV_dbo_TblProduct.BondType)<>[dbo_tblObligationType].[BondType])) OR
(((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine])) OR
(((DEV_dbo_TblProduct.CategoryId)<>[dbo_tblObligationType].[CategoryID]));