Option Compare Database
Option Explicit
Const WhrE = "Pol>0" ' EBLAGH
Const WhrT = "Pol>0 And Emtiaz>0" ' TAHVIL
Const Make_Temp_Amalkard = "INSERT INTO Temp_Amalkard (IDNahi,N,E) " & _
"SELECT IDNahi,Count(NumPro) AS N, Sum(Pol) AS E " & _
"FROM TblSabt WHERE (@WHR) " & _
"GROUP BY IDNahi"
Const Update_From_Temp_Amalkrd = "UPDATE NAVAHI as N INNER JOIN Temp_Amalkard AS T ON N.IDNAHI=T.IDNahi " & _
"SET @F1=T.N, @F2=T.E"
Const Make_Temp_Ranks = "INSERT INTO Temp_Ranks (IDNahi,Rank) " & _
"SELECT A.IDNahi, (SELECT COUNT (*)+1 FROM (SELECT @X FROM NAVAHI) AS B WHERE A.@X<B.@X) AS Rank " & _
"FROM NAVAHI AS A"
Const Update_From_Temp_Ranks = "UPDATE Navahi AS N INNER JOIN Temp_Ranks AS T ON N.IDNahi=T.IDNahi " & _
"SET N.@X = T.Rank"
Public Sub CALC(SAL As Long)
PROCESS WhrE, "PEK", "EEK"
PROCESS WhrT, "PTK", "ETK"
PROCESS WhrE & " AND SAL=" & SAL, "PES", "EES"
PROCESS WhrT & " AND SAL=" & SAL, "PTS", "ETS"
Calc_Rank "PAK", "PRK"
Calc_Rank "PASB", "PRSB"
Calc_Rank "EAK", "ERK"
Calc_Rank "EASB", "ERSB"
Calc_Rank "PAWK", "PRWK"
Calc_Rank "PAWSB", "PRWSB"
Calc_Rank "EAWK", "ERWK"
Calc_Rank "EAWSB", "ERWSB"
Calc_Rank "PASA", "PRSA"
Calc_Rank "PAWSA", "PRWSA"
Calc_Rank "EASA", "ERSA"
Calc_Rank "EAWSA", "ERWSA"
DoCmd.RunSQL ("UPDATE NAVAHI SET PTS_SUM=" & DSum("PTS", "NAVAHI"))
DoCmd.RunSQL ("UPDATE NAVAHI SET ETS_SUM=" & DSum("ETS", "NAVAHI"))
End Sub
Private Sub PROCESS(WHR As String, Fld1 As String, Fld2 As String)
DoCmd.RunSQL ("DELETE FROM Temp_Amalkard")
DoCmd.RunSQL (Replace(Make_Temp_Amalkard, "@WHR", WHR))
DoCmd.RunSQL (Replace(Replace(Update_From_Temp_Amalkrd, "@F1", Fld1), "@F2", Fld2))
End Sub
Private Sub Calc_Rank(FldA As String, FldR As String)
DoCmd.RunSQL ("DELETE FROM Temp_Ranks")
DoCmd.RunSQL (Replace(Make_Temp_Ranks, "@X", FldA))
DoCmd.RunSQL (Replace(Update_From_Temp_Ranks, "@X", FldR))
End Sub