How to Discount 2 Most Expensive items?

  • Thread starter Thread starter Jack
  • Start date Start date
J

Jack

Hello all

I need some advice...

I am trying to give a 10% discount on the two most expensive items in a
list.

I have Form which displays a list of items in a subform with their prices

I need to get the TWO Most Expensive items from that list in my subform to
be able to do a calculation on the sum of those Two Items.

I am able to create a RecorSetClone of the subForm.

But am at a loss as to how I find the TWO most Expensive and then sum those
two so I can do my percent Calculation.

Any Ideas?

Thanks
 
Use the TOP predicate in a sql statement. Syntax looks like:

Select Top 2 ItemID, UnitCost
From wherever
Where whatever you where clause is in the list
Order By UnitCost DESC

Iterate the UnitCost field in two records returned by the query to compute
the discount.

Ron W
 
I wrote this function to use your form's RecordsetClone to
do the calculation you asked for. It takes 4 parameters;

1) The name of the field that the key index is based on in
your table. (Or at very least, a field that's unique to
each record)

2) The name of the field that contains the price amount

3) The percent discount you'd like applied (.1 = 10%)

4) The # of most expensive items you'd like the discount
applied to (in your example, 2)


Place this line in your form's OnCurrent() event;

Me!txtMyTextBox = TakePctOff("KeyID", "Price", 0.1, 2)


Place this function in your form's module;

Function TakePctOff(strKeyField As String, _
strPriceField As String, _
dblPctOff As Double, _
Optional iNumItems As Integer = 2) As Variant
Dim rst As DAO.Recordset
Dim dbl As Double
Dim varKey As Variant
Dim i As Integer
Dim j As Integer
Dim var() As Variant
Dim bolGotIt As Boolean
If iNumItems < 1 Then
TakePctOff = Null
Exit Function
End If
Set rst = Me.RecordsetClone
If rst.EOF Then
TakePctOff = Null
Exit Function
Else
For i = 1 To iNumItems
ReDim Preserve var(1, i)
dbl = 0
Do Until rst.EOF
If rst(strPriceField) >= dbl Then
bolGotIt = False
For j = 1 To i
If var(0, j) = rst(strKeyField) Then
bolGotIt = True
Exit For
End If
Next j
If Not bolGotIt Then
varKey = rst(strKeyField)
dbl = rst(strPriceField)
End If
End If
rst.MoveNext
Loop
If dbl > 0 Then
var(0, i) = varKey
var(1, i) = dbl
End If
rst.MoveFirst
Next i
dbl = 0
For j = 1 To i - 1
dbl = dbl + var(1, j)
Next j
dbl = dbl - (dbl * dblPctOff)
End If
rst.Close
Set rst = Nothing
TakePctOff = dbl
End Function

The beauty of this function is that you don't need to
worry about your records being sorted by price prior to
executing it. The function will find the 2 most expensive
items no matter what order they're in.

I hope you'll post back and let me know if it does the
job. Good luck!
 
Back
Top