help with my weight query

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));
intsupAsked:
Who is Participating?
 
Dale FyeCommented:
The structure of the table you have is going to be a problem.  Anytime you use data (Zone#) as a column header, you will have to write individual queries to accomodate those columns.  And if the number of zones changes, you will have to modify all your queries.  If you organize the table the way I recommended, you would not have to do this.  To get your data into a structure similar to what I recommend you will need to create a "normalization query" which parses this table into a structure like I recommended.  Unfortunately, my internet filters are not allowing me to post the SQL, so I have added an image of what that query would look like.
Normalizing-Query.jpg
0
 
Dale FyeCommented:
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
0
 
intsupAuthor 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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SheilsCommented:
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)


0
 
SheilsCommented:
fyed approach with the rates table is definitely the way to go. I will back out and let him expand on that
0
 
intsupAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.