Access Table Lookup

[Ed F]

I'm a relatively new user and trying to develop a freight
pricing lookup utility. The pricing would be in a matrix
of per hundred weight units. So, the price per lb. up to
100 lbs. would be X; the price between 100 lbs. and 400
lbs. would be Y, etc. Any advice on how to do this would
be greatly appreciated.

 

[Nikos Yannacopoulos]

Ed,

For this example I'll assume you have two fields,
MaxWeight and Freight, i.e. your table looks something
like:

MaxWeight Freight
100 50
400 120
100 200
..
..
..
Make a new query and in design view add the frieght
table, and add field MaxWeight twice and field Freight
once to the grid. Then press the Totals button on the
toolbar (summation sign). In the new line "Totals" that
appears under the fields: change the default Group by to
Where in the first maxWeight, to Min in the second
MaxWeight and to First in the Freight field. Then in the
criteria line in the first field (Where) type

=shipment_weight where shipment_weight represents the

actual weight of your current shipment; you must somehow
input or link this to the current number each time, but I
can't tell you how not knowing what you're doing.

HTH,
Nikos

 

[Guest]

Nikos, thank you for the suggestion.

-----Original Message-----
Ed,

For this example I'll assume you have two fields,
MaxWeight and Freight, i.e. your table looks something
like:

MaxWeight Freight
100 50
400 120
100 200
..
..
..
Make a new query and in design view add the frieght
table, and add field MaxWeight twice and field Freight
once to the grid. Then press the Totals button on the
toolbar (summation sign). In the new line "Totals" that
appears under the fields: change the default Group by to
Where in the first maxWeight, to Min in the second
MaxWeight and to First in the Freight field. Then in the
criteria line in the first field (Where) type
actual weight of your current shipment; you must somehow
input or link this to the current number each time, but I
can't tell you how not knowing what you're doing.

HTH,
Nikos
.