How to make this function more efficient?

[jonathanchye]

Hi all,

I have a table of Couriers and a subtable of Surcharges linked to the Couriers. The table structure is as below:

tblCourier
-PKCourierID
-fkSurchargeID
-txtName
-bolActive

tblSurcharges
-PKSurchargeID
-dteDateActive (the date surcharge starts)
-dblPercentIncrease

My function currently loops through tblSurcharges and tries to work out the latest surcharge based on todays date. This seems to work but highly inefficient. Can anyone recommend a more elegant solution please?

Public Sub CalcFuelPercent()
  '' THIS WILL CHECK FOR DATE OF FUEL SURCHARGE BY ORDER DATE
  On Error Resume Next
   
  If IsNull(Me.Courier) = False And IsNull(Me.OrderDate) = False Then
  'Get PK
  Dim PK As Long
  PK = DLookup("PKCourierID", "tblCourier", "Courier='" & Me.Courier & "'")
  ' Go to last record for Courier charges
  Dim Max As Long
  Max = Nz(DMax("PKSurchargeID", "tblSurcharges", "FKCourier=" & PK & ""), 0)
  ' only do if record exists
  If Max > 0 Then
  Dim temp As Date
  temp = DLookup("DteDateActive", "tblSurcharges", "PKSurchargeID =" & Max & "")
  If temp <= Me.OrderDate Then
  ' Latest date in table <= today
  Me.txtSurchargePercent = Nz(DLookup("DblPercentPercentIncrease", "tblSurcharges", "PKSurchargeID =" & Max & ""), 0)
  Else
  Do
          Max = Max + 1
          temp = DLookup("DteDateActive", "tblSurcharges", "PKSurchargeID =" & Max & "")
  Loop Until temp <= Me.OrderDate
  Me.txtSurchargePercent = Nz(DLookup("DblPercentPercentIncrease", "tblSurcharges", "PKSurchargeID =" & Max & ""), 0)
  ' Reset Values
  End If
  Else
  'No records for fuel surcharge
  Me.txtSurchargePercent.Value = 0
  End If
  Else
  Me.txtSurchargePercent.Value = 0
  End If
  Me.FuelSurcharge = Nz(Me.Price, 0) * (Nz(Me.txtSurchargePercent, 0))
  End Sub

 

[plog]

Create a query based on your surcharge table to get the most recent date for each ID:

SELECT MAX(dteDateActive) As MostRecentSurcharge, PKSurchargeID FROM tblSurcharges GROUP BY PKSurchargeID;

Make a new query with that query and tblSurcharges linking MostRecentSurcharge to dteDateActive and PKSurchargeID to PKSurchargeID and bring in dblPercentIncrease to get the value you need. That query will be the one you can use to determine the most recent dblPercentIncrease for every PKSurchargeID. This assumes that you only have one dteDateActive per PKSurchargeID.

 

[jonathanchye]

Create a query based on your surcharge table to get the most recent date for each ID:

SELECT MAX(dteDateActive) As MostRecentSurcharge, PKSurchargeID FROM tblSurcharges GROUP BY PKSurchargeID;

Make a new query with that query and tblSurcharges linking MostRecentSurcharge to dteDateActive and PKSurchargeID to PKSurchargeID and bring in dblPercentIncrease to get the value you need. That query will be the one you can use to determine the most recent dblPercentIncrease for every PKSurchargeID. This assumes that you only have one dteDateActive per PKSurchargeID.

Hi!

Thanks for your reply. Correct me if I am wrong but your solution seems to just grab the latest date from the surcharges table. The problem is sometimes there will be future dates entered into the table.

My function basically searches through the dates to match the most recent surcharge to today's date (or date order made...). So for example if the surcharges table contains :
1) 1/2/2012 = 2%
2) 7/2/2012 = 5%
3) 10/2/2012 = 10%

If I run function today it will apply 5%. If I run function on or after 10th it will apply 10%.

Looking at your query it will always apply 10%?

 

[plog]

Slap a WHERE clause on my SQL to only include dates up to the current one:

SELECT MAX(dteDateActive) As MostRecentSurcharge, PKSurchargeID FROM tblSurcharges WHERE dteDateActive<=Date GROUP BY PKSurchargeID;

 

[jonathanchye]

Slap a WHERE clause on my SQL to only include dates up to the current one:

SELECT MAX(dteDateActive) As MostRecentSurcharge, PKSurchargeID FROM tblSurcharges WHERE dteDateActive<=Date GROUP BY PKSurchargeID;

Hi, Thanks again!

Tried this but it is possible that the query would return more than 1 value. Using my example above it will return 2 unique values as there are 2 surcharges <= today. I think I need to create another query to then grab the max date of this query and return the surcharge percent?

 

[plog]

SELECT MAX(dteDateActive) As MostRecentSurcharge, PKSurchargeID FROM tblSurcharges WHERE dteDateActive<=Date GROUP BY PKSurchargeID;

That query will not return more than one dteDateActive date per PKSurchargeID.


Are you trying to get the most recent surcharge regardless of ID. If so, this will do that:

MostRecentSurcharge: Dlookup("[dblPercentIncrease]", "tblSurcharges", "[dteDateActive]=#" & DMax("[dteDateActive]", "tblSurcharges", "[dteDateActive]<=#" & Date & "#") & "#")