Hi
I have a problem at the moment in updating a single field in a table. This
is a monetary value based on EITHER the rate per load x # of loads OR the
rate per tonne x total tonnes.
The rate per tonne version works perfectly, but the rate per load always
gives the # of loads as the value - this is a problem with Count(....) The
second problem is that all the values for the Rates per Load Hauliers are
set to 0 when the second part of the SP runs.
Any suggestions please?

CREATE PROCEDURE UPDATE_HAULIERWEIGH
AS
Begin

/* Update Haul_Gbp for Rates per Load - HaulRate.Pay_Per_Load='Y' */
Update Haulier
Set Haul_Gbp=
(Select Count((Weigh.Farm_Code)*HaulRate.Gbp_Per_Load)
From HaulRate, Weigh
Where Haulier.Haul_Code=Weigh.Haul_Code
And Haulier.Haul_Code=HaulRate.Haul_Code
And HaulRate.Farm_Code=Weigh.Farm_Code
And HaulRate.Pay_Per_Load='Y');

/* Update Haul_Gbp for Rates per Tonne - HaulRate.Pay_Per_Load='N' */
Update Haulier
Set Haul_Gbp=
(Select Sum(Weigh.Tonnes*HaulRate.Haul_Rate)
From HaulRate, Weigh
Where Haulier.Haul_Code=Weigh.Haul_Code
And Haulier.Haul_Code=HaulRate.Haul_Code
And HaulRate.Farm_Code=Weigh.Farm_Code
And HaulRate.Pay_Per_Load='N');

end


Alan J Davies
email: alan@...