3 Replies Latest reply on May 22, 2017 8:51 AM by user1069

    Trying to figure out how to search two fields from one field

    JP_1

      I have a table that is for orders which contains a field - Salesperson & a field Salesperson2.  Right now I'm able to search and print a report that subtotals by salesperson.  I just added salesperson2 for sales where 2 salespeople work on and both get commission.  I'm looking to search all orders for a sales person (whether listed they are listed as salesperson or salepserson2).  I know that I can search salesperson then salesperson2 but was looking for a way to search with one field (ie SalespersonAll that looks at both salesperson & salesperson2).  My next hurtle is how to subtotal the report so it groups all orders.  Other than searching salesperson- running the report then searching salesperson2 - running the report then adding them together.....I'm stuck.  Any thoughts or directions would be appreciated! 

        • 1. Re: Trying to figure out how to search two fields from one field
          user1069

          You may want to change your data model before you get too far along with your current structure.

           

          Whenever you are adding "2" to a field name it's a pretty good indication that you should make a new table. So instead of having "salesperson" and "salesperson2" two fields, you would add a new "ORDERSALES" table with the following fields:

           

          ID - primary key for the table

          orderID - foreign key, the primary key for the ORDERS table

          salespersonID - foreign key, the primary key for the SALESPERSON table that contains all of the sales employees

          commission - number, the amount of commission earned for the sale

           

          Putting the commission field in this table enables you to record situations where they don't allocate the commission 50-50. Then you can put your summary fields in this new table as well for your sub-summary reports.

          • 2. Re: Trying to figure out how to search two fields from one field
            JP_1

            If it makes a difference my commissions are in a different table.

            Table: Orders

            This has all the sales order information including the saleperson

            Table: Items

            This contains all the item information including model info - sell prices - commissions

             

            Right now I have salesperson field in "Items" set as a look up back to the "Orders".  I was hoping that there was a way that I could create a field in the items table that looks back at both salesperson fields in "Orders".

            • 3. Re: Trying to figure out how to search two fields from one field
              user1069

              Can we assume the ORDER totals the Commission from all of the ITEMS sold as part of the ORDER?

              The idea of the table to join a SALESPERSON to the ORDER is that you can set the Commission allocation on a per-salesperson basis. So by default you might auto-enter 100% of the ORDER Commission for the SALESPERSON but if there is a second (or third) SALESPERSON you can further adjust their commission.

               

              This structure satisfies your desire to search for a particular Salesperson and see all of the Orders in which that person played a role. It also satisfies your desire to create a report that can total all of the Commissions for the Salesperson. And it's flexible, allowing you to have multiple people involved in the order each receiving commissions and not necessarily in an even split.