Assign price category based on multiple record criteria

[hepsedam]

Hello: I have a database with two tables; tblProduct and tblPrice. I
want a build a query where each Product record is assigned a price,
based on multiple fields (and underlying values) found within
tblProduct. The tblPrice has 234 pricing tiers (records), all with
mutually exclusive criteria.

How would I build this query? I started to go down the IIf, then,
else path, but realized I would have 234 statements. I think I need
some sort of refence formula.

Ex. If a Product's weight is 5 AND its volume is 100, then look within
the Price table to find the Price Record that matches the Product's
record values. tblPrice has the fields MinWeight, MaxWeight,
MinVolume, Max Volume.

Thanks!

 

[vbasean]

Is your price really a calculated value?

if so this would eliminate the need for a price table alltogether.

You could:
create a custom function that computes your price.
or
create an inline computed value in your query.

 

[KARL DEWEY]

Post sample data including what the tblPrice would have. Post how you
calculate it.

 

[hepsedam]

No, the price is not a calculated value. In reality, there are 234
pricing categories, each with defined criteria (max & max numbers,
location, etc). The prices for each fo the 234 price tiers are the
result of the highest bidder--so they are no derived from a formula.
I need to figure out what pricing tier corresponds to each product.

Any specific functions I can use that will reference the Price table
and assign the relevant value to the product record via a query?

Thanks!

 

[hepsedam]

Thanks for the interest so far...here is some sample tables:

tblProduct
Product Weight Volume
Widget A 7 50
Widget B 15 150
Widget C 23 250
Widget D 34 350

tblPrice (the prices are arbitrarily set by sealed bidding--not
calculated)
Price MinWeight MaxWeight MinVolume MaxVolume
$50.00 0 10 0 100
$100.00 10 20 100 200
$200.00 20 30 200 300
$300.00 30 40 300 400

qryResults (this is how my answer should look...but I can't get here)
Product PriceAssign
Widget A $50.00
Widget B $100.00
Widget C $200.00
Widget D $300.00

What would my function be for the query field PriceAssign? Or how
would I accomplish this using code? I am thinking I need some sort of
reference function.

Thanks!

 

[vbasean]

PriceAssigned: DLookup("[Price]","[tblPrice]", "[MinWeight] < " & [Weight] "
And [MaxWeight] > " & [Weight])
you've got two criteria going: Weight and Volume
what if weight and volume don't match?
say, example: MaxWeight is 20 and MinVoulume is 100 but you have a product
that weighs 21 but has a volume of 99
right below the bottom of one teir and above the top of the other.
if volume and weight are mathimatically interchangeable and never conflict
THEN Volume is either a function derived from Weight or vice versa AND if so,
you only need max and min for ONE criteria and not both THEREFORE you only
need to validate the one criteria.

 

[hepsedam]

Thanks for the Dlookup suggestion. However, after much effort I could
not get it to execute reliably. My actual project has 234 mutually
exclusive pricing tiers.

Because of this, and because I need to use MIN and MAX criteria, I end
up with a Dlookup that has many, many AND statements. For whatever
reason, I cannot get Access to evaluate all those AND statements. I
did pay close attention to the apostrophes for either text or number
data.

Does anyone have alternate suggestions? What want to accomplish is
not too difficult conceptually, but for whatever reason it is hard for
me to implement. Basically, I want to specify many "AND" criteria on
a product table. These criteria will define *one* specific pricing
tier out of the 234. Then I want to get the actual price associated
with that distinct tier.

Thanks!

 

[Scott Lichtenberg]

I might be wrong, but it sounds like you have:
1) a product with a weight and volume and
2) a table of bids where people have offered to buy (or sell) the product.

The bids have specified weight and volume limits. You want to find which bid carries the highest (or lowest) price for a product as long as the product's weight and volume fall within the bid limits.

I would do this with SQL. Open a recordset that selects those bids for the given product where the weight and volume are within the bid limits. Try something like this:

Dim db as Database
Dim rs as Recordset
Dim strSQL as String

strSQL = "SELECT * FROM tblPrice WHERE Product = " & MyProduct & " AND MinWeight < " & MyWeight & " AND MaxWeight > " & MyWeight & " AND MinVolume < " & MyVolume & " AND MaxVolume > " & MyVolume " ORDER BY Price "

' Debug.Print strSQL

Set db = CurrentDb
Set rs = db.OpenRecordset (strSQL, dbOpenSnapshot)
If rs.RecordCount = 0
MsgBox "No matching price records were found", vbInformation
Else
rs.MoveLast 'We sorted by price ascending. You can sort by price descending and move first.
MsgBox "Your price is " & rs!Price
End If

rs.Close
Set rs = Nothing
Set db = Nothing

The SQL statements asks Access to return a recordset with all records where:
- the product matches your product
- the minimum weight/volume of the bid is less than the product's
- the maximum weight/volume of the bid is less than the product's

Hope this helps.
Scott

===============

Thanks for the interest so far...here is some sample tables:

tblProduct
Product Weight Volume
Widget A 7 50
Widget B 15 150
Widget C 23 250
Widget D 34 350

tblPrice (the prices are arbitrarily set by sealed bidding--not
calculated)
Price MinWeight MaxWeight MinVolume MaxVolume
$50.00 0 10 0 100
$100.00 10 20 100 200
$200.00 20 30 200 300
$300.00 30 40 300 400

qryResults (this is how my answer should look...but I can't get here)
Product PriceAssign
Widget A $50.00
Widget B $100.00
Widget C $200.00
Widget D $300.00

 

[John Spencer]

SELECT tblProduct.Product
, tblPrice.Price
FROM tblProduct LEFT JOIN tblPrice
ON tblProduct.Weight >=tblPrice.MinWeight
AND tblProduct.Weight <= tblPrice.MaxWeight
AND tblProduct.Volume >= tblPrice.MinVolume
AND tblProduct.Volume <= tblPrice.MaxVolume

I would think that the price table also has a product field, but you did
not mention that in your sample. If that is the case you need to add
the product field to the join

SELECT tblProduct.Product
, tblPrice.Price
FROM tblProduct LEFT JOIN tblPrice
ON tblProduct.Product = tblPrice.Product
AND tblProduct.Weight >=tblPrice.MinWeight
AND tblProduct.Weight <= tblPrice.MaxWeight
AND tblProduct.Volume >= tblPrice.MinVolume
AND tblProduct.Volume <= tblPrice.MaxVolume

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Lance]

You don't need any special function for this.

SELECT *
FROM price, product
WHERE (product.weight between price.minweight and price.maxweight) and
(product.volume between price.minvolume and price.maxvolume)

One problem you're going to have is that if you have a min value the same as
the previous max value you're going to return multiple hits. So you're going
to need to adjust your data.

For example, instead of 100-200 and 200-300 have 101-200 and 201-300.
Alternately you could opt to return only the most or least expensive option.