Updating a field by using formula and where statement 1

[johnbeck]

Hi all,

I am trying to update a field in a table with the sql below. I want to update one field only with the given criteria below. But it gives me the bloody pop-up message "Enter Parameter Value for [FlowSumSKUCountryTON]![Product Code] and others...". Any clues? I tried to put = instead of Like but still it does the same.

Thanks a lot!

~Jbeck

UPDATE Deneme SET Deneme.[Percent] = "[FlowSumSKUCountryTON]![SumOfSKUFlowTon]/[FlowSumUnicaCountryTON]![SumOfUnicaFlow]"
WHERE (((Deneme.[Product Code]) Like [FlowSumSKUCountryTON]![Product Code]) AND ((Deneme.Unica) Like [FlowSumSKUCountryTON]![Unica]) AND ((Deneme.Unica_2) Like [FlowSumSKUCountryTON]![Unica_2]) AND ((Deneme.Countries) Like [FlowSumSKUCountryTON]![Countries]));

 

[PHV]

You may try something like this:
UPDATE Deneme D INNER JOIN FlowSumSKUCountryTON F
ON D.[Product Code] = F.[Product Code] AND D.Unica = F.Unica
SET D.Percent = F.SumOfSKUFlowTon / F.SumOfUnicaFlow
WHERE D.Unica_2 = F.Unica_2 AND D.Countries = F.Countries;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnbeck]

UPDATE (Deneme AS D INNER JOIN FlowSumSKUCountryTON AS F ON (D.[Product Code] = F.[Product Code]) AND (D.Unica = F.Unica)) INNER JOIN FlowSumUnicaCountryTON ON (F.Unica = FlowSumUnicaCountryTON.Unica) AND (F.Countries = FlowSumUnicaCountryTON.Countries) SET D.[Percent] = [F].[SumOfSKUFlowTon]/[FlowSumUnicaCountryTON].[SumOfUnicaFlow]
WHERE (((D.Unica_2)=[F].[Unica_2]) AND ((D.Countries)=[F].[Countries]));

This is what I last came up with. It seems that it will work but a message pops up "Operation must use an updatable query". By the way (lowSumSKUCountryTON AS F) and FlowSumUnicaCountryTON are queries. Deneme AS D is a table. What do you think?

Thanks so much for help,

~Jbeck

 

[PHV]

Perhaps something like this ?
UPDATE Deneme AS D
SET D.Percent = Nz((SELECT S.SumOfSKUFlowTon/U.SumOfUnicaFlow
FROM FlowSumSKUCountryTON AS S INNER JOIN FlowSumUnicaCountryTON AS U
ON S.Unica = U.Unica AND S.Countries = U.Countries
WHERE S.[Product Code] = D.[Product Code] AND S.Unica = D.Unica AND S.Unica_2 = D.Unica_2 AND S.Countries = D.Countries), D.Percent);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886