I am making a simple example just to illustrate what I am trying to accomplish. My table has 18,000 records. I want to just pull out the records where field1+field2+field3 have a different value in field4.
Brenda
>>I have a sales table that has fname,lname,mname,cust_nbr and sales data for each item purchased. Each customer can be in the table multiple times, but his name - lname+fname+mname - should uniquely identify the client and the cust_nbr should also uniquely idenitfy the client. But in my sales table, I think there are are customer names - fname+lname+mname - that have different cust_nbr. How can I write a query to find where f_name+lname+mname are the same, but where they have different cust_nbr?
>>
>>jim,henry,smith,12345
>>jim,henry,smith,44444 && not good, should have 12345 as the cust_nbr
>>
>>Thanks
>>
>>Brenda
>
>
>SELECT fname,lname,mname,cust_nbr;
> FROM ....;
> GROUP BY fname,lname,mname,cust_nbr;
>INTO CURSOR crsTest
>
>that way you will get a cursor the will have all records (and duplicate ones) i.e.
>you will have:
>
>jim,henry,smith,12345
>and
>jim,henry,smith,44444
>
>BUT To beleave that ALL names are unique? Did you know how many John Smith exists in the world? Or Ivan Ivanov?
>Make sure in your tables that
>jim,henry,smith,12345 is different that jim,henry,smith,44444.