Does a filter query? 2nd Post Please Help!

[Guest]

I have an update query based of a filter query but showing the wrong results.
It show the results from the target table and not the source table. Here is
the set up.

Update Query..

Tbl 1 is source tbl based off filter query
Tbl 2 is my target table

Join is on the product ID

I only show results from the target saide and not from the source side. In
other words when my results show I see the info in my target table and not my
source. The correct records and the amount of records show as the results but
it is from the wrong side. How can this happen? If I change the table in the
update query then I see the right ones but it wont update to the correct
table. What is going on here>? I tried everything for 2 days and cant fix
this minor problem. PLEASE HELP IF YOU CAN. Below is my SQL..

UPDATE DEV_dbo_TblProduct INNER JOIN tblProductVersionRefill ON
DEV_dbo_TblProduct.ProductId = tblProductVersionRefill.ProductId SET
DEV_dbo_TblProduct.ProductId = TblProductVersionRefill!productid,
DEV_dbo_TblProduct.MinCreditScore = TblProductVersionRefill!MinCreditScore,
DEV_dbo_TblProduct.BondAmtStd = TblProductVersionRefill!BondAmtStd,
DEV_dbo_TblProduct.Description = TblProductVersionRefill.NewBusinessFormId,
DEV_dbo_TblProduct.BondAmtMax = TblProductVersionRefill!BondAmtMax,
DEV_dbo_TblProduct.BondEffectiveDate =
TblProductVersionRefill!BondEffectiveDate, DEV_dbo_TblProduct.RateCode =
TblProductVersionRefill!RateCode, DEV_dbo_TblProduct.FormId =
TblProductVersionRefill.NewBusinessFormId, DEV_dbo_TblProduct.CancelDays =
TblProductVersionRefill.CancelDays, DEV_dbo_TblProduct.SpecialInstructions =
TblProductVersionRefill!SpecialInstructions,
DEV_dbo_TblProduct.BondExpirationDate =
TblProductVersionRefill!BondExpirationDate, DEV_dbo_TblProduct.BondTermMonths
= TblProductVersionRefill!BondTermMonths, DEV_dbo_TblProduct.RiskType =
TblProductVersionRefill!RiskType, DEV_dbo_TblProduct.RateType =
TblProductVersionRefill!RateType, DEV_dbo_TblProduct.Attachments =
TblProductVersionRefill.Attachments, DEV_dbo_TblProduct.RenewalMethod =
TblProductVersionRefill!RenewalMethod, DEV_dbo_TblProduct.Countersignatures =
TblProductVersionRefill.Countersignatures, DEV_dbo_TblProduct.BondAmtMin =
TblProductVersionRefill!BondAmtMin, DEV_dbo_TblProduct.EAndOFlag =
TblProductVersionRefill.EAndOFlag
WHERE
(((DEV_dbo_TblProduct.ProductId)=[TblProductVersionRefill]![productid]) AND
((DEV_dbo_TblProduct.MinCreditScore)<>[TblProductVersionRefill].[MinCreditScore]))
OR
(((DEV_dbo_TblProduct.Description)<>[TblProductVersionRefill].[Description])
AND
((DEV_dbo_TblProduct.BondEffectiveDate)<>[TblProductVersionRefill].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.RateCode)<>[TblProductVersionRefill].[RateCode]) AND
((DEV_dbo_TblProduct.BondTermMonths)<>[TblProductVersionRefill].[BondTermMonths])
AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([TblProductVersionRefill]![Attachments],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.Attachments)<>"[tblProductVersion].[Attachments]"))
OR (((DEV_dbo_TblProduct.BondAmtStd)<>[TblProductVersionRefill]![BondAmtStd])
AND ((DEV_dbo_TblProduct.BondAmtMax)<>[TblProductVersionRefill].[BondAmtMax])
AND
((DEV_dbo_TblProduct.FormId)<>[TblProductVersionRefill].[NewBusinessFormId])
AND
((DEV_dbo_TblProduct.SpecialInstructions)<>[TblProductVersionRefill].[SpecialInstructions])
AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[TblProductVersionRefill].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.RiskType)<>[TblProductVersionRefill].[RiskType]) AND
((DEV_dbo_TblProduct.RenewalMethod)<>[TblProductVersionRefill].[RenewalMethod]))
OR
(((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([TblProductVersionRefill]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[TblProductVersionRefill].[Countersignatures]")
AND
((DEV_dbo_TblProduct.BondAmtMin)<>[TblProductVersionRefill].[BondAmtMin])) OR
(((DEV_dbo_TblProduct.CancelDays)<>[TblProductVersionRefill].[CancelDays])
AND ((DEV_dbo_TblProduct.RateType)<>[TblProductVersionRefill]![RateType]) AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([TblProductVersionRefill]![EAndOFlag],0)=-1,"Y","N")
And (DEV_dbo_TblProduct.EAndOFlag)<>"[TblProductVersionRefill].[EAndOFlag]"));

 

[John Spencer]

UPDATE queries update fields in the target.
They don't show fields from the source.
If you switch to the datasheet view, the Access will show you the fields and
the fields' current values that will be updated for the records specified.
When you execute the query, the query won't display anything. It will just
do the update to the table.

If you want to see which values are going to be set from the source table
then you have a separate SELECT query.

I have an update query based of a filter query but showing the wrong
results.
It show the results from the target table and not the source table. Here
is
the set up.

Update Query..

Tbl 1 is source tbl based off filter query
Tbl 2 is my target table

Join is on the product ID

I only show results from the target saide and not from the source side. In
other words when my results show I see the info in my target table and not
my
source. The correct records and the amount of records show as the results
but
it is from the wrong side. How can this happen? If I change the table in
the
update query then I see the right ones but it wont update to the correct
table. What is going on here>? I tried everything for 2 days and cant fix
this minor problem. PLEASE HELP IF YOU CAN. Below is my SQL..

UPDATE DEV_dbo_TblProduct INNER JOIN tblProductVersionRefill ON
DEV_dbo_TblProduct.ProductId = tblProductVersionRefill.ProductId SET
DEV_dbo_TblProduct.ProductId = TblProductVersionRefill!productid,
DEV_dbo_TblProduct.MinCreditScore =
TblProductVersionRefill!MinCreditScore,
DEV_dbo_TblProduct.BondAmtStd = TblProductVersionRefill!BondAmtStd,
DEV_dbo_TblProduct.Description =
TblProductVersionRefill.NewBusinessFormId,
DEV_dbo_TblProduct.BondAmtMax = TblProductVersionRefill!BondAmtMax,
DEV_dbo_TblProduct.BondEffectiveDate =
TblProductVersionRefill!BondEffectiveDate, DEV_dbo_TblProduct.RateCode =
TblProductVersionRefill!RateCode, DEV_dbo_TblProduct.FormId =
TblProductVersionRefill.NewBusinessFormId, DEV_dbo_TblProduct.CancelDays =
TblProductVersionRefill.CancelDays, DEV_dbo_TblProduct.SpecialInstructions
=
TblProductVersionRefill!SpecialInstructions,
DEV_dbo_TblProduct.BondExpirationDate =
TblProductVersionRefill!BondExpirationDate,
DEV_dbo_TblProduct.BondTermMonths
= TblProductVersionRefill!BondTermMonths, DEV_dbo_TblProduct.RiskType =
TblProductVersionRefill!RiskType, DEV_dbo_TblProduct.RateType =
TblProductVersionRefill!RateType, DEV_dbo_TblProduct.Attachments =
TblProductVersionRefill.Attachments, DEV_dbo_TblProduct.RenewalMethod =
TblProductVersionRefill!RenewalMethod,
DEV_dbo_TblProduct.Countersignatures =
TblProductVersionRefill.Countersignatures, DEV_dbo_TblProduct.BondAmtMin =
TblProductVersionRefill!BondAmtMin, DEV_dbo_TblProduct.EAndOFlag =
TblProductVersionRefill.EAndOFlag
WHERE
(((DEV_dbo_TblProduct.ProductId)=[TblProductVersionRefill]![productid])
AND
((DEV_dbo_TblProduct.MinCreditScore)<>[TblProductVersionRefill].[MinCreditScore]))
OR
(((DEV_dbo_TblProduct.Description)<>[TblProductVersionRefill].[Description])
AND
((DEV_dbo_TblProduct.BondEffectiveDate)<>[TblProductVersionRefill].[BondEffectiveDate])
AND ((DEV_dbo_TblProduct.RateCode)<>[TblProductVersionRefill].[RateCode])
AND
((DEV_dbo_TblProduct.BondTermMonths)<>[TblProductVersionRefill].[BondTermMonths])
AND
((DEV_dbo_TblProduct.Attachments)=IIf(Nz([TblProductVersionRefill]![Attachments],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Attachments)<>"[tblProductVersion].[Attachments]"))
OR
(((DEV_dbo_TblProduct.BondAmtStd)<>[TblProductVersionRefill]![BondAmtStd])
AND
((DEV_dbo_TblProduct.BondAmtMax)<>[TblProductVersionRefill].[BondAmtMax])
AND
((DEV_dbo_TblProduct.FormId)<>[TblProductVersionRefill].[NewBusinessFormId])
AND
((DEV_dbo_TblProduct.SpecialInstructions)<>[TblProductVersionRefill].[SpecialInstructions])
AND
((DEV_dbo_TblProduct.BondExpirationDate)<>[TblProductVersionRefill].[BondExpirationDate])
AND ((DEV_dbo_TblProduct.RiskType)<>[TblProductVersionRefill].[RiskType])
AND
((DEV_dbo_TblProduct.RenewalMethod)<>[TblProductVersionRefill].[RenewalMethod]))
OR
(((DEV_dbo_TblProduct.Countersignatures)=IIf(Nz([TblProductVersionRefill]![Countersignatures],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.Countersignatures)<>"[TblProductVersionRefill].[Countersignatures]")
AND
((DEV_dbo_TblProduct.BondAmtMin)<>[TblProductVersionRefill].[BondAmtMin]))
OR
(((DEV_dbo_TblProduct.CancelDays)<>[TblProductVersionRefill].[CancelDays])
AND ((DEV_dbo_TblProduct.RateType)<>[TblProductVersionRefill]![RateType])
AND
((DEV_dbo_TblProduct.EAndOFlag)=IIf(Nz([TblProductVersionRefill]![EAndOFlag],0)=-1,"Y","N")
And
(DEV_dbo_TblProduct.EAndOFlag)<>"[TblProductVersionRefill].[EAndOFlag]"));

 

[norwedsh]

John,

I'm just learning SQL, and was wonder if the above code is indicative
of typical SQL code?

Eric

 

[John Spencer]

I can't say it is typical.

It is probably more complex than most SQL statements that a beginner sees.
And Access adds a lot of unneeded parentheses to the query which can
actually make it harder to understand what is going on.

There also seem to be parts of this SQL in the WHERE clause that don't make
sense to me. The code seems to be attempting to reference another table -
tblProductVersion- and at times has field and table names in quotes.