Determine weight between 2 weights

[bustersports]

Hi,

I have a database that is used to determine shipping rates based on multiple factors. I am trying to determine what the weight break would be when given a shipment weight. Below is the table layout and an example of what I am looking to get.

Table
MinWgt MaxWgt Zone Rate Minimum ServiceLevel
151 299.99 2 $0.18 $72.00 NH
300 499.99 2 $0.16 $72.00 NH
500 999.99 2 $0.15 $72.00 NH
1000 1999.99 2 $0.14 $72.00 NH
2000 4999.99 2 $0.12 $72.00 NH
5000 9999.99 2 $0.10 $72.00 NH

There are multiple zones and service levels, such as Zones 1-10 with up to 6 different service levels for each.

Examples

Weight - 464.0731 Zone 2 ServiceLevel NH - rate found should be $.16 from weight 300 - 499.99
Weight - 151.0966 Zone 2 ServiceLevel NH - rate found should be $.18 from weight 151 - 299.99
Weight - 558.9126 Zone 2 ServiceLevel NH - rate found should be $.15 from weight 500 - 999.99
Weight - 5686.9604 Zone 2 ServiceLevel NH - rate found should be $.10 from weight 5000 - 9999.99

The problem I am having is I am only getting the rate if it matches exactly i.e. 499.99, which almost never happens. What I tried to do was put the following criteria in a query on the Max Weight - Between [Forms]![frmShipment].[Form]![txtWeight] And [Forms]![frmShipment].[Form]![txtWeight], no rates are returned (have tried against MinWeight as well, same result).

The only way I have been able to come up with a solution is to have a rate for each weight, zone, and service level, which would create thousands of extra records. Obviously not real practical.

Any help would be greatly appreciated. Thanks very much in advance.

 

[PWise]

[Forms]![frmShipment].[Form]![txtWeight] between MinWgt  and MaxWgt

 

[SkipVought]

hi,

Convert TEXT to NUMBER!!!

     [MinWgt]>= CInt([Forms]![frmShipment].[Form]![txtWeight])
 And [MinWgt]<= CInt([Forms]![frmShipment].[Form]![txtWeight])

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

If you're doing this in code, you could use a CASE statement.
Something like...

Select Case ItemWeight
    Case < 300
        rate = .18
    Case < 500
        rate = .16
    Case < 1000
        rate = .15
    Case < 2000
        rate = .14
    Case < 5000
        rate = .12
    Case < 10000
        rate = .10
    Case else
        rate = ???
End Select

You could even take it a step further, if necessary and nest your case statements. Something like this, perhaps.

Select Case ItemWeight
    Case < 300:
        Select Case Zone
            Case 1:
                rate = .##
                minimum = ##
                servicelevel = "??"
            Case 2:
                rate = .18
                minimum = 72
                servicelevel = "NH"
        End Select
    Case < 500:
        Select Case Zone
            Case 1:
            Case 2:
                rate = .16
                minimum = 72
                servicelevel = "NH"
etc

Randy

 

[bustersports]

Skip,

Sorry, the txtWeight field is a number field, the title is only incorrect. In what field of the query to you suggest I put your response in, MinWgt or MaxWgt?

My query would need to return the min and max weights, rate, zone, and service level. Needed so the user can verify if they so desire.

Thanks

 

[bustersports]

Randy700,

The rates change frequently, so if I understand your suggestion properly, this would require the code to be changed each time a rate changes. Am I correct?

Thanks for your help.

 

[randy700]

yes.
Actually, you should put the values in a table.
Perhaps a dlookup function within the case statement.

Exactly how are you trying to use this?


Randy

 

[bustersports]

Randy700,

The intention is to display rates to a user during an audit what the shipment paid amount was, display what the rate should have been to allow the user to mark the payment as correct or not. I do have the data into the following table structure.

Min Weight Max Weight Service Level Zone Rate
1000 99999.9 ON C $1.49
968.001 968.5 AC A $2.40
968.001 968.5 AC B $4.18
968.001 968.5 NH C $4.80
968.001 968.5 ON D $6.04
968.001 968.5 AC E $6.87
968.001 968.5 AC F $9.11

 

[PHV]

What is your actual SQL code and where are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bustersports]

Thanks to all. I was able to get the recommendation from SkipVought to work.

 

[bustersports]

All,

Having a different type of problem with the same form. When opening the form and making the data selections, the data form -subfrmSelectCharge4FedExUSNHSHSI does not update properly, however if I go into design form and reopen screen in view form, it works correctly everytime. I am doing a requery of the data subform after user makes final data selection - Me.[subfrmSelectCharge4FedExUSNHSHSI].Requery. This problem only exists when using the sql from query 1 below, no problem with using query 2. The only difference between the 2 is no weight criteria in the query 2 vs query 1 with - <=CInt([Forms]![frmSelectChargeFedExFreightUSNHSHSI]![subfrmSelectCharge2FedExFreightUSNHSHSI].[Form]![txtWeight]) and
>=CInt([Forms]![frmSelectChargeFedExFreightUSNHSHSI]![subfrmSelectCharge2FedExFreightUSNHSHSI].[Form]![txtWeight])
I have included the sql for both queries below. The problem exists even if I remove either the minimum or the maximum criteria.

Query that does not work when form opened without going into design view first.

Query 1

SELECT tblFedExRates.MinWeight, tblFedExRates.MaxWeight, tblFedExRates.Rate, tblFedExRates.Minimum, tblFedExRates.Zone, tblFedExRates.ServiceLevel, tblFedExRates.USDomesticRate
FROM tblFedExRates
GROUP BY tblFedExRates.MinWeight, tblFedExRates.MaxWeight, tblFedExRates.Rate, tblFedExRates.Minimum, tblFedExRates.Zone, tblFedExRates.ServiceLevel, tblFedExRates.USDomesticRate
HAVING (((tblFedExRates.MinWeight)<=CInt([Forms]![frmSelectChargeFedExFreightUSNHSHSI]![subfrmSelectCharge2FedExFreightUSNHSHSI].[Form]![txtWeight])) AND ((tblFedExRates.MaxWeight)>=CInt([Forms]![frmSelectChargeFedExFreightUSNHSHSI]![subfrmSelectCharge2FedExFreightUSNHSHSI].[Form]![txtWeight])) AND ((tblFedExRates.Zone)=[Forms]![frmSelectChargeFedExFreightUSNHSHSI]![subfrmSelectCharge2FedExFreightUSNHSHSI].[Form]![txtFedExDomesticZone]) AND ((tblFedExRates.ServiceLevel)=[Forms]![frmSelectChargeFedExFreightUSNHSHSI]![cboSvcLevelCode]) AND ((tblFedExRates.USDomesticRate)=-1));

Query 2
Query that works everytime without going into design mode first. SELECT tblFedExRates.MinWeight, tblFedExRates.MaxWeight, tblFedExRates.Rate, tblFedExRates.Minimum, tblFedExRates.Zone, tblFedExRates.ServiceLevel, tblFedExRates.USDomesticRate
FROM tblFedExRates
GROUP BY tblFedExRates.MinWeight, tblFedExRates.MaxWeight, tblFedExRates.Rate, tblFedExRates.Minimum, tblFedExRates.Zone, tblFedExRates.ServiceLevel, tblFedExRates.USDomesticRate
HAVING (((tblFedExRates.Zone)=[Forms]![frmSelectChargeFedExFreightUSNHSHSI]![subfrmSelectCharge2FedExFreightUSNHSHSI].[Form]![txtFedExDomesticZone]) AND ((tblFedExRates.ServiceLevel)=[Forms]![frmSelectChargeFedExFreightUSNHSHSI]![cboSvcLevelCode]) AND ((tblFedExRates.USDomesticRate)=-1));

I closed Access many times including recompiling the modules. Any suggestions as to what the issue maybe?

Thanks very much for any help you can provide.