Update Solution. Need Help

[troy]

I currenty have 7 fileds from 4 tables (source). I would like the data if it
has changed in one filed or even all seven if they have changed to be updated
into my source table. The problem is the source table has a primary key but
it is not necessary to update that field of course. How would I go about
setting up the query to make this happen. I have tried everything from not
show or moving th primay key to checking all properites etc.All tables are
included in the query set. Here is my SQL if needed. Thank you in advance!

UPDATE dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN
(dbo_TblProductVersion INNER JOIN (DEV_dbo_TblProduct RIGHT JOIN
dbo_TblProduct ON DEV_dbo_TblProduct.ProductId = dbo_TblProduct.ProductId) ON
dbo_TblProductVersion.ProductId = dbo_TblProduct.ProductId) ON
dbo_tblObligationType.ObligationTypeID = dbo_TblProduct.ObligationTypeID) ON
dbo_tblSaaDocument.SaaDocumentID = dbo_tblObligationType.SaaDocumentID SET
DEV_dbo_TblProduct.ProductId = [dbo_tblProduct]![ProductID],
DEV_dbo_TblProduct.StateAbbr = [dbo_TblProduct]![StateAbbr],
DEV_dbo_TblProduct.ProductLine = [dbo_tblObligationType]![ProductLine],
DEV_dbo_TblProduct.BondAmtStd = [dbo_TblProductVersion]![BondAmtStd],
DEV_dbo_TblProduct.BondAmtMax = [dbo_TblProductVersion]![BondAmtMax],
DEV_dbo_TblProduct.BondType = [dbo_tblObligationType]![BondType],
DEV_dbo_TblProduct.BondTermMonths = [dbo_TblProductVersion]![BondTermMonths],
DEV_dbo_TblProduct.CancelDays = [dbo_TblProductVersion]![CancelDays],
DEV_dbo_TblProduct.ObligationTypeID = [dbo_TblProduct]![ObligationTypeID],
DEV_dbo_TblProduct.CategoryId = [dbo_tblObligationType]![CategoryID],
DEV_dbo_TblProduct.BondExpirationDate =
[dbo_TblProductVersion]![BondExpirationDate],
DEV_dbo_TblProduct.AvailableOnline = [dbo_TblProduct]![Available Online],
DEV_dbo_TblProduct.MinCreditScore = [dbo_TblProductVersion]![MinCreditScore],
DEV_dbo_TblProduct.BondEffectiveDate =
[dbo_TblProductVersion]![BondEffectiveDate], DEV_dbo_TblProduct.ClassCode =
[dbo_tblSaaDocument]![ClassCode], DEV_dbo_TblProduct.RiskType =
[dbo_TblProductVersion]![RiskType], DEV_dbo_TblProduct.RateType =
[dbo_TblProductVersion]![RiskType], DEV_dbo_TblProduct.RenewalMethod =
[dbo_TblProductVersion]![RenewalMethod], DEV_dbo_TblProduct.RateCode =
[dbo_TblProductVersion]![RateCode],
DEV_dbo_TblProduct.BondObligationDescription =
[dbo_TblProduct]![BondObligationDescription], DEV_dbo_TblProduct.ObligeeId =
[dbo_TblProduct]![ObligeeID], DEV_dbo_TblProduct.SpecialInstructions =
[dbo_TblProductVersion]![SpecialInstructions], DEV_dbo_TblProduct.Description
= [dbo_tblProductVersion].[Description], DEV_dbo_TblProduct.FormId =
[dbo_tblProductVersion].[NewBusinessFormId], DEV_dbo_TblProduct.BondAmtMin =
[dbo_TblProductVersion]![BondAmtMin], DEV_dbo_TblProduct.Attachments =
[dbo_TblProductVersion].[Attachments], DEV_dbo_TblProduct.Countersignatures =
[dbo_TblProductVersion].[Countersignatures], DEV_dbo_TblProduct.EAndOFlag =
[dbo_TblProductVersion].[EAndOFlag]
WHERE (((DEV_dbo_TblProduct.ProductId)<>[dbo_tblProduct]![ProductID])) OR
(((DEV_dbo_TblProduct.StateAbbr)<>[dbo_tblProduct].[Stateabbr]) AND
((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([dbo_tblProductversion]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[dbo_tblProductVersion].[Countersignatures]"))
OR (((DEV_dbo_TblProduct.ProductLine) Not Like
[dbo_tblObligationType].[ProductLine]) AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[dbo_TblProductVersion].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.AvailableOnline)<>[dbo_TblProduct].[Available
Online]) AND
((DEV_dbo_TblProduct.MinCreditScore)<>[dbo_TblProductVersion].[MinCreditScore])
AND
((DEV_dbo_TblProduct.BondEffectiveDate)<>[dbo_TblProductVersion].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.ClassCode)<>[dbo_tblSaaDocument].[ClassCode]) AND
((DEV_dbo_TblProduct.RiskType)<>[dbo_TblProductVersion].[RiskType]) AND
((DEV_dbo_TblProduct.RateType)<>[dbo_TblProductVersion]![RateType]) AND
((DEV_dbo_TblProduct.BondAmtMin)<>[dbo_TblProductVersion].[BondAmtMin]) AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([dbo_tblProductversion]![Attachments],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Attachments)<>"[dbo_tblProductVersion].[Attachments]")
AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([dbo_tblProductversion]![EAndOFlag],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.EAndOFlag)<>"[dbo_tblProductVersion].[EAndOFlag]"))
OR (((DEV_dbo_TblProduct.BondAmtStd)<>[dbo_TblProductVersion]![BondAmtStd])
AND ((DEV_dbo_TblProduct.CategoryId)<>[dbo_tblObligationType].[CategoryID])
AND
((DEV_dbo_TblProduct.RenewalMethod)<>[dbo_TblProductVersion].[RenewalMethod])
AND
((DEV_dbo_TblProduct.FormId)<>[dbo_TblProductVersion].[NewBusinessFormId]))
OR (((DEV_dbo_TblProduct.BondAmtMax)<>[dbo_TblProductVersion].[BondAmtmax])
AND ((DEV_dbo_TblProduct.RateCode)<>[dbo_TblProductVersion].[RateCode]) AND
((DEV_dbo_TblProduct.Description)<>[dbo_TblProductVersion].[Description])) OR
(((DEV_dbo_TblProduct.BondType) Not Like [dbo_tblObligationType].[BondType])
AND ((DEV_dbo_TblProduct.CancelDays)<>[dbo_TblProductVersion].[CancelDays])
AND
((DEV_dbo_TblProduct.ObligationTypeID)<>[dbo_TblProduct].[ObligationTypeID])
AND
((DEV_dbo_TblProduct.BondObligationDescription)<>[dbo_TblProduct].[BondObligationDescription])
AND
((DEV_dbo_TblProduct.SpecialInstructions)<>[dbo_TblProductVersion].[SpecialInstructions]))
OR
(((DEV_dbo_TblProduct.BondTermMonths)<>[dbo_TblProductVersion].[BondTermMonths]) AND ((DEV_dbo_TblProduct.ObligeeId)<>[dbo_TblProduct].[ObligeeId]))
WITH OWNERACCESS OPTION;

 

[John Vinson]

I currenty have 7 fileds from 4 tables (source). I would like the data if it
has changed in one filed or even all seven if they have changed to be updated
into my source table. The problem is the source table has a primary key but
it is not necessary to update that field of course. How would I go about
setting up the query to make this happen. I have tried everything from not
show or moving th primay key to checking all properites etc.All tables are
included in the query set. Here is my SQL if needed. Thank you in advance!

I strongly suspect that this will be a LOT easier if you run four
separate queries, rather than joining all four tables together. The
queries can be run from a macro, or (much better since you can trap
errors) from VBA code.

Note that you might want to use OR logic on the criteria to determine
that at least one field has changed - but it's not really necessary to
update only that field. If the value hasn't changed, then updating
$123.50 to $123.50 won't really hurt anything!

John W. Vinson[MVP]

 

[troy]

Do you think I am receiving a type conversion error because of the foreign
key in one of my source tables? It is not related to any of the tables in my
query set nor is the field in my query set. I may try to break down the
queries and see what happends. Thank you and could you get back to me about
the error. OH I thought I was using the "OR" creteria?

 

[John Vinson]

Do you think I am receiving a type conversion error because of the foreign
key in one of my source tables? It is not related to any of the tables in my
query set nor is the field in my query set. I may try to break down the
queries and see what happends. Thank you and could you get back to me about
the error. OH I thought I was using the "OR" creteria?

Troy, you've posted a huge SQL statement concerning a database which I
cannot see; I don't know the datatypes or structure of your tables,
and I am busy with paying work, and really don't have the time to dig
through all your issues here.

Again: let me suggest that you KEEP IT SIMPLE.

Rather than one monstrous query to do four updates at the same time,
*just run four queries*. Try setting it up to update your (redundant,
improperly normalized) master table from JUST ONE of the related
tables.

Then try it with a new query, with a different one of the tables.

Then run both these queries from a Macro or from code.

Then add the third table, as a third query; then add the fourth table,
as a fourth query.

I wish I could be more help, but this would go beyond what's
reasonable for an unpaid volunteer.

John W. Vinson[MVP]

 

[troy]

No problem I understand. I took your advice and it works fine excetpfor that
dang extra primary key. I am trying to figure out what I can put in my
creteria so my new table will not accept the dupe product numbers when I run
my append query. Other than that it works fine and appreciate the help!

 

[John Vinson]

No problem I understand. I took your advice and it works fine excetpfor that
dang extra primary key. I am trying to figure out what I can put in my
creteria so my new table will not accept the dupe product numbers when I run
my append query. Other than that it works fine and appreciate the help!

YOu don't need another "primary key" to prevent entering multiple
product numbers. Just put a unique Index on the product number field.

John W. Vinson[MVP]

 

[troy]

John,

The company will not allow me to touch the properties. It is on a SQL back
end and they dont want to make it any more unstable. YEA RIGHT! THIS THING IS
A MESS! Any other ideas besides a filter query then base it off that? Thanks
for the help!