We help IT Professionals succeed at work.

help with my weight query

intsup
intsup asked
on
Medium Priority
305 Views
Last Modified: 2012-05-11
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:
qryPN_ShipWt
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
FROM tblPN;


qryShipRateGroundFedEx_MaxDt
SELECT tblShipRateGroundFedEx.Weight, tblShipRateGroundFedEx.[Zone 8], Max(tblShipRateGroundFedEx.ImportDate) AS MaxOfImportDate
FROM tblShipRateGroundFedEx
GROUP BY tblShipRateGroundFedEx.Weight, tblShipRateGroundFedEx.[Zone 8]
HAVING (((tblShipRateGroundFedEx.Weight)>0));
Comment
Watch Question

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
Another way around this would be to create a FEDEX_Rates table.  Since I don't know anything about FEDEX, they might have fixed rates for some weight ranges, and the weight rates ($/#) for other weight ranges.  In that case, this table might have a structure which looks like:

Zone   MinWt    MaxWt     FixedRate        WtRate
   1           0             5              $5
   1           5            10             $7
   1          10           20           $10
   1          20           70           $20
   1          70         150           $40
   1        150         999                                 0.25

This type of table structure would allow you to create a query that joins on the Zone field, and has a WHERE clause similar to:

SELECT yourTable.*, NZ(FedEx_Rates.FixedRate, yourTable.Weight * FedEx_Rates.WtRate) as ShippingCost
FROM yourTable INNER JOIN FedEx_Rates on yourTable.Zone = FedEx_Rates.Zone
WHERE yourTable.Weight >= FedEx_Rates.MinWt and yourTable.Weight < FedEx_Rates.MaxWt

Author

Commented:
actually, i did download the fedex ground rates table and that table is called tblShipRateGroundFedEx and looks like:

Weight  Zone1  Zone 2 ... ... ... Zone 8
1            $1        $2                    $4
2            $2        $2.50               $5
...
150        $30      $32                  $40

Commented:
This:

if     [qryPN_ShipWt]!ShipWeight>150 then  [qryShipRateGroundFedEx_MaxDt]![Zone 8]/150*[qryPN_ShipWt]!ShipWeight

Can be written as:

IIF( [qryPN_ShipWt]!ShipWeight>150,[qryShipRateGroundFedEx_MaxDt]![Zone 8]/150*[qryPN_ShipWt]!ShipWeight,false Expression)


Commented:
fyed approach with the rates table is definitely the way to go. I will back out and let him expand on that
Owner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
sorry for the delay.  i was able to get another table which had the missing weights and used that instead of trying to do a workaround.  i didn't try your solution but will award it to you nonetheless.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.