Solved to return a ID value if criteria is met

SQL:
SELECT
  e.EmployeeID,
  e.[Total of Pay Check],
  e.[Salary Pay Period],
  t.Percent
FROM tblEmployees e
INNER JOIN tblpayrolltaxes t
        ON e.[Filing Status] = t.[Filing Status]
       AND e.[Total of Pay Check] BETWEEN t.[Minimum Inc] AND t.[Maximum Inc]
Change the field names to fit according to your pdf you posted in Post
I'm doing it in a query
 
SQL:
SELECT
  e.EmployeeID,
  e.[Total of Pay Check],
  e.[Salary Pay Period],
  e.[Filing Status],
  t.Percent
FROM tblEmployees e
INNER JOIN tblpayrolltaxes t
        ON e.[Filing Status] = t.[Filing Status]
       AND e.[Salary Pay Period] = t.[Payroll Period]
       AND e.[Total of Pay Check] >= t.[Minimum Inc] 
       AND e.[Total of Pay Check] < t.[Maximum Inc]

The above is a query - based on what you posted in the pdf

What happens if you try it? (Note I adjusted it a bit from the version you quoted)
 
Last edited:
SQL:
SELECT
  e.EmployeeID,
  e.[Total of Pay Check],
  e.[Salary Pay Period],
  e.[Filing Status],
  t.Percent
FROM tblEmployees e
INNER JOIN tblpayrolltaxes t
        ON e.[Filing Status] = t.[Filing Status]
       AND e.[Salary Pay Period] = t.[Payroll Period]
       AND e.[Total of Pay Check] >= t.[Minimum Inc]
       AND e.[Total of Pay Check] < t.[Maximum Inc]

The above is a query - based on what you posted in the pdf

What happens if you try it? (Note I adjusted it a bit from the version you quoted)
This gave me errors also.
 
I have the calculations for all 3 level working by this.

Taxes 12760: IIf([tblEmployees].[Basic Salary] Between 0 And 12760,[tblEmployees].[Basic Salary])/([tblEmployees].[Salary Pay Period])

Taxes 25520: IIf([tblEmployees]![Basic Salary] Between 12760 And 25520,[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period])

Taxes 280950: IIf([tblEmployees]![Basic Salary] Between 25520 And 280950,[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period])

It looks like the attachment. Any suggestions on how do i get them all in one column? I tried summing all the columns but that didn't work.
 

Attachments

"Tax" column:
Code:
IIf([tblEmployees].[Basic Salary] Between 0 And 12760,
[tblEmployees].[Basic Salary])/([tblEmployees].[Salary Pay Period]),
IIf([tblEmployees]![Basic Salary] Between 12760 And 25520,
[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),
IIf([tblEmployees]![Basic Salary] Between 25520 And 280950,
[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),"")))

Noting some edge case overlap

Edited - Strikethru - remove 3 extra closing brackets - well that strikethru is not preserved in code. - just remove the 3 closing brackets after each [Salary Pay Period]
 
Last edited:
"Tax" column:
Code:
IIf([tblEmployees].[Basic Salary] Between 0 And 12760,
[tblEmployees].[Basic Salary])/([tblEmployees].[Salary Pay Period]),
IIf([tblEmployees]![Basic Salary] Between 12760 And 25520,
[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),
IIf([tblEmployees]![Basic Salary] Between 25520 And 280950,
[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/([tblEmployees].[Salary Pay Period]),"")))

Noting some edge case overlap
I tried it, I'm getting "The expression you entered contains invalid syntax, or you need to enclose your text in quotes". It highlights the comma before the 2nd IIf.
 
I corrected it to read, Taxes: IIf([tblEmployees]![Basic Salary] Between [tblpayrolltaxes]![Min] And [tblpayrolltaxes]![Max],[tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Min],0)

It is still calculating people out side of the Min and Max
Well it will as you are not using that as any criteria to reduce the records, just for a calculation. :(
That is why I suggested using 0 for False. That would then show those you have made calculations on.
 
I would write my own function to do this. Easier to debug and understand.

I did for something similar
Code:
Public Function CalcBonus(plngEmployeeID As Long, pdtStartDate As Variant, pdtEndDate As Variant, piBonusPeriod As Integer)
On Error GoTo ErrHandler
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim strEmployee As String, strSQL As String
Dim curBonus As Currency
Dim blnMultiLevel As Boolean
Set db = CurrentDb()

strEmployee = DLookup("[Forename] & ' ' & [Surname]", "tblEmployee", "EmployeeID = " & plngEmployeeID)
SetStatusBar ("Calculating Bonus for " & strEmployee)

' Need to check if commission is multi level. if not plain math will do the trick
blnMultiLevel = Nz(DLookup("CommMultiLevel", "tblEmployee", "[EmployeeID] = " & plngEmployeeID), 0) ' No longer needed?

' BonusCount and
If IsNull(pdtStartDate) Then
    Set qdf = CurrentDb.QueryDefs("qryBonusCommissionNull")
Else
    Set qdf = CurrentDb.QueryDefs("qryBonusCommissionProcessed")
End If
With qdf
    .Parameters("[tmpEmployeeID]") = plngEmployeeID
    .Parameters("[tmpBonusPeriod]") = piBonusPeriod
    If Not IsNull(pdtStartDate) Then
        .Parameters("[tmpStartDate]") = pdtStartDate
        .Parameters("[tmpEndDate]") = pdtEndDate
    End If
End With
Set rst = qdf.OpenRecordset
If rst.EOF Then
    SetStatusBar ("No Bonus Commission records found for " & strEmployee)
    CalcBonus = 0
    Exit Function
End If
rst.MoveFirst
Do Until rst.EOF
    ' Need to check how bonus is calculated?, by count or amount
    Select Case rst.Fields("BonusType")
        Case 2  ' Count
            curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And Level <= " & rst!BonusCount & ""), 0)
        Case 3 ' Amount
            curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And LevelAmount <= " & rst!SumOfCommAmount & ""), 0)
    End Select
    CalcBonus = CalcBonus + curBonus
    ' Still need to keep track of bonus count
    TempVars("BonusCount") = TempVars("BonusCount") + rst!BonusCount
    rst.MoveNext
Loop

rst.Close
'Debug.Print "Bonus Count " & TempVars("BonusCount")
'Debug.Print "Bonus Value" & CalcBonus
ExitFunction:
    Set db = Nothing
    Set rst = Nothing
    Set qdf = Nothing

Err_Exit:
    Exit Function
    
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitFunction
    
End Function
 
Code:
Tax: IIf([tblEmployees].[Basic Salary] Between 0 And 12760;[tblEmployees].[Basic Salary]/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 12760 And 25520;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 25520 And 280950;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];"")))
 
Code:
Tax: IIf([tblEmployees].[Basic Salary] Between 0 And 12760;[tblEmployees].[Basic Salary]/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 12760 And 25520;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];IIf([tblEmployees]![Basic Salary] Between 25520 And 280950;([tblEmployees].[Basic Salary]-[tblpayrolltaxes]![Lower])/[tblEmployees].[Salary Pay Period];"")))
Note the different separator :( , so use what you normally use, which appears to be the comma?
 
I would write my own function to do this. Easier to debug and understand.

I did for something similar
Code:
Public Function CalcBonus(plngEmployeeID As Long, pdtStartDate As Variant, pdtEndDate As Variant, piBonusPeriod As Integer)
On Error GoTo ErrHandler
Dim db As Database
Dim rst As Recordset
Dim qdf As QueryDef
Dim strEmployee As String, strSQL As String
Dim curBonus As Currency
Dim blnMultiLevel As Boolean
Set db = CurrentDb()

strEmployee = DLookup("[Forename] & ' ' & [Surname]", "tblEmployee", "EmployeeID = " & plngEmployeeID)
SetStatusBar ("Calculating Bonus for " & strEmployee)

' Need to check if commission is multi level. if not plain math will do the trick
blnMultiLevel = Nz(DLookup("CommMultiLevel", "tblEmployee", "[EmployeeID] = " & plngEmployeeID), 0) ' No longer needed?

' BonusCount and
If IsNull(pdtStartDate) Then
    Set qdf = CurrentDb.QueryDefs("qryBonusCommissionNull")
Else
    Set qdf = CurrentDb.QueryDefs("qryBonusCommissionProcessed")
End If
With qdf
    .Parameters("[tmpEmployeeID]") = plngEmployeeID
    .Parameters("[tmpBonusPeriod]") = piBonusPeriod
    If Not IsNull(pdtStartDate) Then
        .Parameters("[tmpStartDate]") = pdtStartDate
        .Parameters("[tmpEndDate]") = pdtEndDate
    End If
End With
Set rst = qdf.OpenRecordset
If rst.EOF Then
    SetStatusBar ("No Bonus Commission records found for " & strEmployee)
    CalcBonus = 0
    Exit Function
End If
rst.MoveFirst
Do Until rst.EOF
    ' Need to check how bonus is calculated?, by count or amount
    Select Case rst.Fields("BonusType")
        Case 2  ' Count
            curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And Level <= " & rst!BonusCount & ""), 0)
        Case 3 ' Amount
            curBonus = Nz(DMax("BonusAmount", "qryEmployeeBonus", "employeeID=" & plngEmployeeID & " AND ProductID= " & rst!ProductID & " And LevelAmount <= " & rst!SumOfCommAmount & ""), 0)
    End Select
    CalcBonus = CalcBonus + curBonus
    ' Still need to keep track of bonus count
    TempVars("BonusCount") = TempVars("BonusCount") + rst!BonusCount
    rst.MoveNext
Loop

rst.Close
'Debug.Print "Bonus Count " & TempVars("BonusCount")
'Debug.Print "Bonus Value" & CalcBonus
ExitFunction:
    Set db = Nothing
    Set rst = Nothing
    Set qdf = Nothing

Err_Exit:
    Exit Function
   
ErrHandler:
    MsgBox "Error " & Err.Number & " " & Err.Description
    Resume ExitFunction
   
End Function
I'm definitely not that advanced.
 
I'm definitely not that advanced.
Yours would not be anywhere near that complicated.
Just pass in what you need and use a Select Case statement.
Return the value.
 

Users who are viewing this thread

Back
Top Bottom