help with my weight query
Posted on 2011-04-18
i have a query based on FedEx shipping rates and the weights of parts we carry.
the problem i have is that the fedex ground rates only go up to 150 lbs but some parts are over 150 lbs in weight. instead of getting into yet another query for freight shipments, i would simply like to take the Zone 8 rate for 150 lb shipments, divide it by 150 then multiply it by the actual weight over 150 lbs.
here is my queries:
Query name: qryGrossShipCost
Query sql: SELECT qryPN_ShipWt.idn, qryPN_ShipWt.MfgPart, qryPN_ShipWt.ShipWeight, qryShipRateGroundFedEx_MaxDt.[Zone 8] AS GrossShipCost
FROM qryShipRateGroundFedEx_MaxDt INNER JOIN qryPN_ShipWt ON qryShipRateGroundFedEx_MaxDt.Weight = qryPN_ShipWt.ShipWeight;
i want something similar to this which did not work:
if [qryPN_ShipWt]!ShipWeight>150 then [qryShipRateGroundFedEx_MaxDt]![Zone 8]/150*[qryPN_ShipWt]!ShipWeight
thanks for your help!
just in case you want to view the two queries that compose this query, here they are:
SELECT tblPN.idn, tblPN.MfgPart, [dvolume]/166 AS DimWt, tblPN.Weight AS ActualWt, Round(IIf(IIf([DimWt]>[ActualWt],[DimWt],[ActualWt])=0,1,IIf([DimWt]>[ActualWt],[DimWt],[ActualWt]))+0.5,0) AS ShipWeight
SELECT tblShipRateGroundFedEx.Weight, tblShipRateGroundFedEx.[Zone 8], Max(tblShipRateGroundFedEx.ImportDate) AS MaxOfImportDate
GROUP BY tblShipRateGroundFedEx.Weight, tblShipRateGroundFedEx.[Zone 8]