Results 1 to 6 of 6
  1. #1
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143

    Dynamic Incremental Counter. Keeping track of which sale was first, second, third...

    We pay our sales team a commission. The first sale in a new account they get a higher %, and then incrementally lower % until sale 3+



    I have a query that counts the number of orders for each store, but if they make a first and second sale within the same pay-period, I want to be able to tell which is first and which is second so that they get the correct % for each order.
    And I would like to be able to look back at previous pay-period reports and see the amount they were paid for their first few sales in an account even after there are 3+ sales.

    What I have so far is two queries, the first counts the orders per store after 1/1/2017:

    qrySalesCount
    Code:
    SELECT tblRetailersActive.RetailID, Count(tblRetailersActive.RetailID) AS CountOfRetailID, tblOrderConf.IsSample, tblRetailersActive.PrivDesc
    FROM tblRetailersActive INNER JOIN tblOrderConf ON tblRetailersActive.RetailID = tblOrderConf.RetailerID
    WHERE (((tblOrderConf.DelDate)>#1/1/2017#))
    GROUP BY tblRetailersActive.RetailID, tblOrderConf.IsSample, tblRetailersActive.PrivDesc
    HAVING (((tblOrderConf.IsSample)=No) AND ((tblRetailersActive.PrivDesc) Not Like "*Producer*" And (tblRetailersActive.PrivDesc) Not Like "*processor*"));

    The second calculates the commission amount based on the number of orders per store:

    qryComAmt
    Code:
    SELECT tblRetailersActive.Tradename, tblOrderConf.Comtbl, tblEmployees.FirstN, tblEmployees.LastN, qrySalesCount.CountOfRetailID, IIf([CountOfRetailID]=1,0.2,IIf([CountOfRetailID]=2,0.1,0.05)) AS ComAmt
    FROM (tblRetailersActive INNER JOIN qrySalesCount ON tblRetailersActive.RetailID = qrySalesCount.RetailID) INNER JOIN ((tblOrderConf INNER JOIN tblOrderDetails ON (tblOrderConf.ConfTableID = tblOrderDetails.ConfTableID) AND (tblOrderConf.ConfTableID = tblOrderDetails.ConfTableID)) INNER JOIN tblEmployees ON tblOrderConf.ComEntorEmp = tblEmployees.Employee) ON tblRetailersActive.RetailID = tblOrderConf.RetailerID
    GROUP BY tblRetailersActive.Tradename, tblOrderConf.Comtbl, tblEmployees.FirstN, tblEmployees.LastN, qrySalesCount.CountOfRetailID;
    As a test I made sure to attribute the commission to one store with only 1 order, and the 2nd order of another store with 8 orders. As is, it only counts commission based on the total number of orders.


    Thanks for any help I can get!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the tSales table would have a SaleID (autonum)
    or at least a date.
    it would be the earliest date for that month, then pick that SalesPersonID.

  3. #3
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    I guess I would like a way for the query to determine the order of sales so that a user can pick a pay-period and open a report displaying the sales and what % will be attributed to the sale based on the order they were delivered.

  4. #4
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Any thoughts on how to make a query that attributes a 1, 2, 3... to records based on the customer and date delivered? OR how to count the number of records with the same customer only if the delivery date is less than the delivery date of the record in question?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    PeakH is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    143
    Thanks! Allen Browne has great information. I ended up with two queries, one that displayed the orders to be counted, and the second to rank them:

    qrySalesCount
    Code:
    SELECT tblRetailersActive.RetailID, tblOrderConf.IsSample, tblRetailersActive.PrivDesc, tblOrderConf.ConfTableID, tblOrderConf.DelDate
    FROM tblRetailersActive INNER JOIN tblOrderConf ON tblRetailersActive.RetailID = tblOrderConf.RetailerID
    WHERE (((tblOrderConf.IsSample)=No) AND ((tblRetailersActive.PrivDesc) Not Like "*Producer*" And (tblRetailersActive.PrivDesc) Not Like "*processor*") AND ((tblOrderConf.DelDate)>#2/1/2017#));
    qrySalesOrder
    Code:
    SELECT qrySalesCount.ConfTableID, qrySalesCount.RetailID, qrySalesCount.DelDate, (Select Count([RetailID]) AS OrderCount FROM qrySalesCount AS Dupe WHERE Dupe.DelDate < qrySalesCount.DelDate AND Dupe.RetailID = qrySalesCount.RetailID) AS OrderRank
    FROM qrySalesCount
    ORDER BY qrySalesCount.RetailID DESC;

    Only thing now is that I can't have two orders to the same customer on the same day. Which should be fine.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Keeping track
    By IgnorantDatabaseUser in forum Access
    Replies: 3
    Last Post: 01-22-2018, 12:19 PM
  2. Keeping track of work hours
    By UT227 in forum Database Design
    Replies: 2
    Last Post: 10-17-2016, 08:06 AM
  3. Replies: 3
    Last Post: 07-17-2016, 06:48 PM
  4. alphabet incremental counter function
    By paccatore in forum Programming
    Replies: 18
    Last Post: 10-02-2015, 11:34 AM
  5. Keeping track of groups
    By DJDJDJDJ in forum Forms
    Replies: 1
    Last Post: 08-30-2012, 02:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums