I have a table that contains prices from several agents and from several airlines, they were divided into groups by weight charges.
Depending on the airport of departure (POL/C) and arrival (POD/C), I check all the prices and I have to use the best one. But to show the price alternatives.
Each airline has its own method of calculation, therefore I have to check.
table contains the following information:
From the beginning it will have two weights as follows:
1. actual total weight (GW - gross weight)
2. calculated weight by volume (VW)
if GW > VW then..
calculation is based on the higher value (GW)
else
calculation is based on the higher value (VW)
example:
VW = 405 kgs and GW = 222 kgs then use higher value
FSC and SSC is added to the price if any.
Where is calculated on weight (VW) and If ScGw = Yes THEN the weight is different account and is calculated using (GW)
example:
Air freight = euro 0.25 / kgs (x 405 kgs VW)
Fuel + security = euro 1.1 / kgs (x 222 kgs GW)
If ScGw = No THEN calculate the normal VW
example:
Air freight = euro 0.25 / kgs (x 405 kgs VW)
Fuel + security = euro 1.1 / kgs (x 405 kgs VW)
If the calculation is made according to GW, then add the FSC and SSC automatically and without having to count, if ScGw = Yes / No
Values of GW and VW we have already calculated in another form and only need to be use. Airport of departure (POL/C) and arrival (POD/C) is already selected in another form.
If you can help me, as a few days simply fail to find any solution. I am writing full pages without any good result.
Thanks to all who respond.
Depending on the airport of departure (POL/C) and arrival (POD/C), I check all the prices and I have to use the best one. But to show the price alternatives.
Each airline has its own method of calculation, therefore I have to check.
table contains the following information:
Code:
ID = AutoNumber, Long Integer
A/CODE = Number, Long Integer
AGENT = Text,
POL/C = Text,
POL = Text,
POD/C = Text,
POD = Text,
IATA = Text,
Airline = Text,
UPDATE = Date/Time, Short Date
EXPIRY DATE = Date/Time, Short Date
CURRENCY = Text,
M/M = Number, Double (Minimum weight accepted)
-45 = Number, Double (price for the weight between 1 and 45)
+45 = Number, Double (price for the weight starting from 45 to 100)
+100 = Number, Double (price for the weight starting from 100 to 300)
+300 = Number, Double (price for the weight starting from 300 to 500)
+500 = Number, Double (price for the weight starting from 500 to 1000)
+1000 = Number, Double (price for the weight starting from 1000)
FSC = Number, Double
SSC = Number, Double
ScGw = Yes/No, Yes/No
FREQUENCY = Text,
TT = Number, Long Integer
T/S = Yes/No, Yes/No1. actual total weight (GW - gross weight)
2. calculated weight by volume (VW)
if GW > VW then..
calculation is based on the higher value (GW)
else
calculation is based on the higher value (VW)
example:
VW = 405 kgs and GW = 222 kgs then use higher value
FSC and SSC is added to the price if any.
Where is calculated on weight (VW) and If ScGw = Yes THEN the weight is different account and is calculated using (GW)
example:
Air freight = euro 0.25 / kgs (x 405 kgs VW)
Fuel + security = euro 1.1 / kgs (x 222 kgs GW)
If ScGw = No THEN calculate the normal VW
example:
Air freight = euro 0.25 / kgs (x 405 kgs VW)
Fuel + security = euro 1.1 / kgs (x 405 kgs VW)
If the calculation is made according to GW, then add the FSC and SSC automatically and without having to count, if ScGw = Yes / No
Values of GW and VW we have already calculated in another form and only need to be use. Airport of departure (POL/C) and arrival (POD/C) is already selected in another form.
If you can help me, as a few days simply fail to find any solution. I am writing full pages without any good result.
Thanks to all who respond.
Code:
Public Sub CalculPret()
Dim db As Database
Dim rec As Recordset
Dim PolCboV As String
Dim PodCboV As String
Dim strSQL As String
Dim GrossWeight As Double
Dim VolumeWeight As Double
Dim CalcWeight As Double
Dim CalcWeightScGw As Double
Dim CalcPrice As Variant
Dim TotalPrice As Double
PolCboV = [Forms]![DimensionsQry]![PolCbo]
PodCboV = [Forms]![DimensionsQry]![PodCbo]
'"Prices_List" is a table to save all offers from all agents
strSQL = "SELECT Prices_List.ID, Prices_List.[A/CODE], Prices_List.AGENT, " & _
"Prices_List.[POL/C], Prices_List.POL, Prices_List.[POD/C], " & _
"Prices_List.POD, Prices_List.IATA, Prices_List.AIRLINE, " & _
"Prices_List.UPDATE, Prices_List.[EXPIRY DATE], Prices_List.CURRENCY, " & _
"Prices_List.[M/M], Prices_List.[-45], Prices_List.[+45], " & _
"Prices_List.[+100], Prices_List.[+300], Prices_List.[+500], " & _
"Prices_List.[+1000], Prices_List.FSC, Prices_List.SSC, Prices_List.ScGw, " & _
"Prices_List.FREQUENCY, Prices_List.TT, Prices_List.[T/S]"
strSQL = strSQL & " FROM Prices_List"
strSQL = strSQL & " WHERE (((Prices_List.[POL/C])='" & PolCboV & "') " & _
"AND ((Prices_List.[POD/C])='" & PodCboV & "'));"
Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL)
If rec.RecordCount = 0 Then
rec.Close
Exit Sub
Else
GrossWeight = [Forms]![DimensionsQry]![Text34]
VolumeWeight = [Forms]![DimensionsQry]![Text36]
If GrossWeight > VolumeWeight Then
CalcWeight = GrossWeight
Else
If ScGw = "Yes" Then
CalcWeight = GrossWeight
Else
CalcWeight = VolumeWeight
End If
End If
rec.MoveFirst
Do Until rec.EOF
Select Case CalcWeight
Case 1 To 44
CalcPrice = IIf(rec![-45] Is Null, "No Value", rec![-45])
Case 45 To 99
CalcPrice = IIf(rec![+45] Is Null, "No Value", rec![+45])
Case 100 To 299
CalcPrice = IIf(rec![+100] Is Null, "No Value", rec![+100])
Case 300 To 499
CalcPrice = IIf(rec![+300] Is Null, "No Value", rec![+300])
Case 500 To 999
CalcPrice = IIf(rec![+500] Is Null, "No Value", rec![+500])
Case Is >= 1000
CalcPrice = IIf(rec![+1000] Is Null, "No Value", rec![+1000])
End Select
'This part is not completed
'not all agents have prices for all weight groups
'so may be empty fields
'This affects the calculation below
If CalcWeight = GrossWeight Then
CalcPrice = CalcPrice + rec!FSC + rec!SSC
TotalPrice = CalcPrice * CalcWeight
Else
TotalPrice = (CalcPrice * CalcWeight) + ((rec!FSC + rec!SSC) * GrossWeight)
End If
'message is only for the moment to test results.
'results have to collect in a table of all price options
'sorted from lowest to highest
'for now I have not figured out how to make
MsgBox rec!AGENT & " - " & TotalPrice & " " & rec!CURRENCY & " " & rec!Airline
rec.MoveNext
Loop
End If
rec.Close
End Sub