Solved to return a ID value if criteria is met

cheekybuddha said:
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
Click to expand...
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:
cheekybuddha said:
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)
Click to expand...
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:
GaP42 said:
"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
Click to expand...
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.
 
dullster said:
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
Click to expand...
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];"")))
 
CarlettoFed said:
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];"")))
Click to expand...
Note the different separator :( , so use what you normally use, which appears to be the comma?
 
Gasman said:
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
Click to expand...
I'm definitely not that advanced.
 
dullster said:
I'm definitely not that advanced.
Click to expand...
Yours would not be anywhere near that complicated.
Just pass in what you need and use a Select Case statement.
Return the value.
 
You must log in or register to reply here.

Similar threads

D
Solved Creating a query with 1 table and 4 queries adds a new record
Replies
10
Views
388
Pat Hartman
P
D
Solved Query showing all records instead of just the ones that meet the criteria
Replies
3
Views
265
The_Doc_Man
The_Doc_Man
D
After update
Replies
17
Views
619
Pat Hartman
P
D
Solved Running a public sub when form gets focus
Replies
2
Views
300
dobseh
D
D
Subform requery is starting a new record
Replies
4
Views
436
dullster
D

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom