Determine weight between min and max 2

[bustersports]

Hi,

Thanks in advance for any help.

What I am trying to do is calculate what the rate should be on a shipment. I have the rate and min weight and max weight. I get via an input the weight of the shipment. I need to determine what that shipment rate we should be charged.

Table examples

Rate Table

Min Weight Max Weight Rate
0.00 0.50 $12.99
0.51 1.00 $14.29
1.01 1.50 $15.49
1.51 2.00 $16.67
2.01 2.50 $17.76
2.51 3.00 $18.85
3.01 3.50 $19.92

Shipment info table

Shipment Weight

1 .511 lbs
2 1.0 lbs
3 2.5 lbs
4 2.51 lbs

What I need to know is how to determine is:

Shipment 1 - rate would be $14.29
Shipment 2 - rate would be $14.29
Shipment 3 - rate would be $17.76
Shipment 4 - rate would be $18.85

In summary, I need to be able to determine what the rate of a shipment is, given the weight can be between min and max.
I hope this is clear. Thanks again.

 

[MajP]

my field names are a little different but something like
SELECT
tblWt.wt,
tblOne.minWt,
tblOne.maxWt,
tblOne.rate
FROM tblOne, tblWt
WHERE
tblWt.wt >=[minwt] And tblWt.wt<=[maxwt]

 

[bustersports]

Thanks MajP, it worked great. Another question. How do I pull the correct data out of the rates table when I have multiple regional possibilities? I know I can get the data splitting the table into multiple tables, but that would result in about 50 additional tables, which does not see like the right thing to do. Below is the table I am referring to.

Rate ID Min Weight Max Weight NANA NASA NAEU
1 0.00 0.50 $18.76 $21.59 $23.07
2 0.51 1.00 $21.02 $24.12 $25.77
3 1.01 1.50 $23.30 $26.65 $28.44
4 1.51 2.00 $25.56 $29.13 $31.11
5 2.01 2.50 $27.82 $31.58 $33.81
6 2.51 3.00 $29.94 $34.03 $36.48
7 3.01 3.50 $32.25 $36.48 $39.18

Table definitions:
Rate ID - autonumber
Min Weight - Minimum weight for this rate
Max Weight - Maximum weight for this rate
NANA - Region
NASA - Region
NAEU - Region

Using the query you helped with, what I need to be able to do is pull the correct data by the region. I have the region and weight being displayed, want the user to be able to verify the correct rate was charged.

Example:

If I have a shipment of 1.5lbs from the NAEU region, I want the form to display $28.44.

Hope this makes sense.

Thanks.

 

[MajP]

Your table is not really normalize. You should have had it set up like.
tblRateData
RateID
MinWeight
MaxWeight
Region (text field "NANA", "NASA", "NAEU"
Rate

But you can do this after the fact with a Union normalizing query.
qryRateData

SELECT 
 tblRateData.RateID, 
 tblRateData.MinWeight, 
 tblRateData.MaxWeight,
  "NANA" AS Region,
  tblRateData.NANA AS Rate
FROM tblRateData
UNION
SELECT 
 tblRateData.RateID, 
 tblRateData.MinWeight, 
 tblRateData.MaxWeight,
  "NASA" AS Region,
  tblRateData.NASA AS Rate
FROM tblRateData
UNION
SELECT 
 tblRateData.RateID, 
 tblRateData.MinWeight, 
 tblRateData.MaxWeight,
  "NAEU" AS Region,
  tblRateData.NAEU AS Rate
FROM tblRateData

now the data looks like

RateID	MinWeight	MaxWeight	Region	Rate
1	0	0.5	NAEU	$23.07
1	0	0.5	NANA	$18.76
1	0	0.5	NASA	$21.59
2	0.51	1	NAEU	$25.77
2	0.51	1	NANA	$21.02
2	0.51	1	NASA	$24.12
3	1.01	1.5	NAEU	$28.44
3	1.01	1.5	NANA	$23.30
3	1.01	1.5	NASA	$26.65
4	1.51	2	NAEU	$31.11
4	1.51	2	NANA	$25.56
4	1.51	2	NASA	$29.13
5	2.01	2.5	NAEU	$33.81
5	2.01	2.5	NANA	$27.82
5	2.01	2.5	NASA	$31.58
6	2.51	3	NAEU	$36.48
6	2.51	3	NANA	$29.94
6	2.51	3	NASA	$34.03
7	3.01	3.5	NAEU	$39.18
7	3.01	3.5	NANA	$32.25
7	3.01	3.5	NASA	$36.48

So you would use this query and the where would look more like
tblWt.wt >=qryRateData.[minweight] And tblWt.wt<=qryRateData.[maxweight] And tblWt.Region = qryRateData.region

 

[bustersports]

A very large THANK YOU. It work great, sweet.

 

[danvlas]

I wouldn't use:

wt>=minweight and wt<=maxweight,

because at limits you can get bad/ambiguous/no results. For instance, a weight of 0.505 would not be found

I would use:

wt > minweight and wt<=maxweight
and set MinWeight={Previous MaxWeight}

HTH


Daniel Vlas
Systems Consultant