Quantity Pricing

[Mike Thomas]

This is more of a strategy question - we are going to begin to price goods
based on the quanity break from suppliers.

There is a part table with the partkey, part #, supplier key, MOQ etc.
There is also a part_cost table with
pcKey
partkey
lower_quantity
price

I am trying to figure out a graceful way to run an SQL query which will
extract a price based on order qunatity. One option would be:

SELECT price FROM part_cost WHERE partkey = lng_MyPartKey

then loop thru the recordset testing lower_quantity until the right one is
found. This is a very bad fit with existing queries and reports where only
one record is needed.

Another possibility would be to add a field to the part_cost table suach as
'upper_quantity', then do:

SELECT price FROM part_cost WHERE partkey = lng_MyPartKey AND
lng_MyQuantity >= part_cost.lower_quantity AND lng_MyQuantity <=
part_cost.upper_quantity

but this seems combersome, and I have to figure out a way to deal with
upper_quantity when it is in the record with the last break, eg any quantity
over, for example, 200 gets price X (or a situation where there is no
quantity break, eg any order >= 1 gets the same price.) I want to avoid
setting the last upper bound to some very high number, say one million, to
hopefully cover all situations.

This must be a common scenario. Is there a more clean way to deal with it?

Many thanks
Mike Thomas

 

[Allen Browne]

"SELECT TOP 1 price FROM part_cost
WHERE (partkey = " & lng_MyPartKey & ") AND (" &
lng_MyQuantity & " >= lower_quantity) ORDER BY lower_quantity;"

A subquery would be the most efficient way to do this within a query.

Alternatively, if you just need a lookup value, you could use this function
which works like DLookup() but lets you specify a sort order. The syntax
would be:
=ELookup("price", "part_cost", "partkey = " & lng_MyPartKey,
"lower_quantity")

Function ELookup(Expr As String, Domain As String, Optional Criteria,
Optional OrderClause)
On Error GoTo Err_ELookup
'Purpose: Faster and more flexible replacement for DLookup()
'Arguments: Same as DLookup, with additional Order By option.
'Author: Allen Browne. (e-mail address removed)
'Examples:
'1. To find the last value, include DESC in the OrderClause, e.g.:
' ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
'2. To find the lowest non-null value of a field, use the Criteria,
e.g.:
' ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
'Note: Requires a reference to the DAO library.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSql As String

'Build the SQL string.
strSql = "SELECT TOP 1 " & Expr & " FROM " & Domain
If Not IsMissing(Criteria) Then
strSql = strSql & " WHERE " & Criteria
End If
If Not IsMissing(OrderClause) Then
strSql = strSql & " ORDER BY " & OrderClause
End If
strSql = strSql & ";"

'Lookup the value.
Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset(strSql, dbOpenForwardOnly)
If rs.RecordCount = 0 Then
ELookup = Null
Else
ELookup = rs(0)
End If
rs.Close

Exit_ELookup:
Set rs = Nothing
Set db = Nothing
Exit Function

Err_ELookup:
MsgBox Err.Description, vbExclamation, "ELookup Error " & Err.Number
Resume Exit_ELookup
End Function


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

This is more of a strategy question - we are going to begin to price goods
based on the quanity break from suppliers.

There is a part table with the partkey, part #, supplier key, MOQ etc.
There is also a part_cost table with
pcKey
partkey
lower_quantity
price

I am trying to figure out a graceful way to run an SQL query which will
extract a price based on order qunatity. One option would be:

SELECT price FROM part_cost WHERE partkey = lng_MyPartKey

then loop thru the recordset testing lower_quantity until the right one is
found. This is a very bad fit with existing queries and reports where only
one record is needed.

Another possibility would be to add a field to the part_cost table suach as
'upper_quantity', then do:

SELECT price FROM part_cost WHERE partkey = lng_MyPartKey AND
lng_MyQuantity >= part_cost.lower_quantity AND lng_MyQuantity <=
part_cost.upper_quantity

but this seems combersome, and I have to figure out a way to deal with
upper_quantity when it is in the record with the last break, eg any quantity
over, for example, 200 gets price X (or a situation where there is no
quantity break, eg any order >= 1 gets the same price.) I want to avoid
setting the last upper bound to some very high number, say one million, to
hopefully cover all situations.

This must be a common scenario. Is there a more clean way to deal with

it?