4 Replies Latest reply: Jul 13, 2010 11:42 AM by nikbirkin RSS

    SQL WHERE LIKE query -

      i have in one table tblcustomers a field custords. in this field i have both sales orders and credits posted. Each prefixed with SO for sales order and CR for credits. I have attempt to run a query to list only credits and or only sales orders. script is pretty standard

      select * from tblcustomers where custords like 'CR%';

      ideally i would ike to define the return as credits in the above example.

      I apologise if this has been answered elsewhere.

      any pointers will be gratefully received.

       

        • SQL WHERE LIKE query -
          John Witherspoon

          Is this what you're asking for? I'm not sure I understand the question.

          load *,mid(custords,3) as credits;
          select * from tblcustomers where custords like 'CR%';

            • SQL WHERE LIKE query -

              Thanks John, sorry if the question was unclear - I hope to clarify below:-

              In my Access Database I have a table tbCustomers within the table I have a column for custords - the data in the custords column reflewct both sales and credits sales are prefixed with SO ( Sales Order ) and Credits with CR. The format of each is SO12345 CR12345 each is a unique number.

              I have created some impressive dashboard tabs- but a query is to product queries to list only Credits - without showing Sales Orders and Vice a Versa

              This is why I thought upon using the Select Where Like Statement - In reality I am looking to undertake 2 selections from the same data source renaming each as select where queries one for CR loaded as Credits and the Other SO as Sales. As such I am looking to perform the select where statement as part of the load in order to define the result as Credits or sales.

              regards

               

              Nik

               

                • SQL WHERE LIKE query -
                  John Witherspoon

                  Sounds like this, then:

                  [Table]:
                  load *,mid(custords,3) as credits;
                  select * from tblcustomers where custords like 'CR%';
                  concatenate ([Table])
                  load *,mid(custords,3) as salesorders;
                  select * from tblcustomers where custords like 'SO%';

                  This might be a more efficient approach, but you'd have to test:

                  [Table]:
                  LOAD *
                  ,recno() as ID
                  ,left(custords,2) as type
                  ;
                  SELECT * FROM tblcustomers
                  ;
                  LEFT JOIN ([Table])
                  LOAD
                  ID
                  ,mid(custords,3) as credits
                  RESIDENT [Table]
                  WHERE type = 'CR'
                  ;
                  LEFT JOIN ([Table])
                  LOAD
                  ID
                  ,mid(custords,3) as salesorders
                  RESIDENT [Table]
                  WHERE type = 'SO'
                  ;