help with my weight query

Posted on 2011-04-18
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:
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
FROM tblShipRateGroundFedEx
GROUP BY tblShipRateGroundFedEx.Weight, tblShipRateGroundFedEx.[Zone 8]
HAVING (((tblShipRateGroundFedEx.Weight)>0));
Question by:intsup
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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 Comment

    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
    LVL 16

    Expert Comment


    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)

    LVL 16

    Expert Comment

    fyed approach with the rates table is definitely the way to go. I will back out and let him expand on that
    LVL 47

    Accepted Solution

    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.

    Author Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now