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

your figures are confusing. can you upload a db with the tables

Hmm, are you looking for a "trigger" which is called Data Macro or to you want to do it manually in VBA?

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.

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.

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.

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.

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.

How would you like to start?

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

Select allOpen in new window

I am sorry I am working on this please don't delete

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?

Okay where does this go?

In a module