I need a trigger for the user to know when a query hits a particular running balance total.

We pay commission levels based on gross profit.  If my salesmen hit 300000 the commission will move up from 9% to 12%.  I run payroll twice a month.  I need it to trigger me when we go over the 300000 and I need to know what is it behavior.  So if we are at 275000 in gross profit when the query reads the next record and the gross profit of 30000.  I need that the salesmen has gone to the next level but the calculation needs to do 25000 at 9% and the next 5000 at 12%.  I don't know VB and I am doing this with a select query and then a append query.  I do write each record into a paydetail table.  Any advice would be welcomed.  The signal can be a conditional format on the query but I do need two lines written in the table one at 25000 at 9% and then again 5000 at 12%.  I have three levels this needs to happen.
cres1121Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
your figures are confusing. can you upload a db with the tables
0
ste5anSenior DeveloperCommented:
Hmm, are you looking for a "trigger" which is called Data Macro or to you want to do it manually in VBA?
0
cres1121Author Commented:
I don't want to do it manually but because I don't know VBA that scares me.    I really would like the code just to do the calculation for me and then write it to the table.  I do it with a select query and then a append it just when I get to the changing of percentages that it becomes tricky.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ste5anSenior DeveloperCommented:
Then take a look at data macros. For the rest Rey is right, without a concise example, e.g. a sample database, it's hard to tell what you exactly need.
0
aikimarkCommented:
You have used both 30000 (30k) and 300000 (300k) as well as other values that are an order of magnitude different from one another.  This is confusing.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Also, this sounds very much like a "Do this for me" instead of "Help me do this".

If you can spell out what you need, and show us what you've got, we can probably help you to write the code. But we won't write it for you.
0
cres1121Author Commented:
Noi would prefer to do it I just need lots of direction.  Even with the way I asked a question as you can see.  I would appreciate a mentor.  The more I know the more I can help my company.
0
cres1121Author Commented:
How would you like to start?
0
PatHartmanCommented:
This is a liner process rather than a set operation so you need to do it with code.  You can't efficiently do it with queries.

You need to start with a query that sorts the records by salesman and date.  Open the query in a recordset.  As you read each record, you need to compare the salesmanID to the one you are processing.  You also need to keep a running total of sales.  based on the total sales, calculate the commission using the correct rate.

Here is a very basic idea of how the code goes.
Dim HoldSalesmanID As Long
Dim SaleAmt as Currency
Dim qd As DAO.Querydef
Dim db as DAO.Database
Dim rs  As DAO.Recordset
   Set db = CurrentDB()
    Set qd = db.Querydefs!queryname
    set rs = qd.OpenRecordset
    HoldSalesmanID = rs!SalesmanID
    SaleAmt = 0    
        Do Until rs.EOF
            If  rs!SalesmanID = HoldSalesmanID Then
                 GoSub UpdateRec
            Else
                 HoldSalesmanID = rsSalesmanID
                 SaleAmt = 0
                 GoSub UpdateRec
            End If
            rs.MoveNext
        Loop

        Set rs = nothing

    Exit Sub
UpdateRec:
            SaleAmt = SaleAmt + rs!Amt
             rs.Edit
            If SaleAmt  > 300000 Then
                rs!Commission = rs!Amt * .12
            Else
                rs!Commission = rs!Amt * .09
            End If
            rs.Update
    Return
End Sub

Open in new window

0
cres1121Author Commented:
I am sorry I am working on this please don't delete
0
cres1121Author Commented:
Hi Pat

No excuses but I am fairly new to VB so .... Here we go...

This is what I really  need and I don't know how to do it.  
We determine commission percent based on gross profit that is paid in a year.  So before I begin I need to determine total gross profit which I am storing gross profit in a tblpaysummary table by invoice number.

Then I determine which additional invoices I pay.  I need to then add and create a payroll record in the tblpaydetail with commission rate and commission.  I am determining the rate based on the total gross profit by salesmen.  We have 3 different levels 12%  for 0 to less than 300,000, 15% for 300000 to less than 600000 and 18% over 600000.  

I use a append query to update the tblpaydetail but I need the vb to write the record with the correct percentage.

So I created my query sorting based on employeeid and transdate.  What do I do next?
0
aikimarkCommented:
Here are a couple of user-defined functions that will return a percentage value for any gross sales.
Option Explicit

Public Function GrossSalesPct(ByVal parmGrossSales) As Single
'We have 3 different levels 12%  for 0 to less than 300,000,
'                           15% for 300000 to less than 600000 and
'                           18% over 600000.
    Select Case parmGrossSales
        Case Is < 300000
            GrossSalesPct = 0.12
        Case Is < 600000
            GrossSalesPct = 0.15
        Case Else
            GrossSalesPct = 0.18
    End Select
End Function


Public Function GrossSalesPct2(ByVal parmGrossSales) As Single
'We have 3 different levels 12%  for 0 to less than 300,000,
'                           15% for 300000 to less than 600000 and
'                           18% over 600000.
    Dim lngBracket As Long
    Static vBrackets As Variant
    If IsEmpty(vBrackets) Then
        vBrackets = Array(0.12, 0.15, 0.18)
    End If
    lngBracket = parmGrossSales \ 300000
    If lngBracket < 3 Then
        GrossSalesPct2 = vBrackets(lngBracket)
    Else
        GrossSalesPct2 = vBrackets(2)
    End If
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cres1121Author Commented:
Okay where does this go?
0
aikimarkCommented:
In a module
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.