Query Quandary (MOM/R&R Infinity)

[simulacra]

I am using MOM 5.4 with R&R Infinity and am trying to accomplish the following:____I need to develop a report of customers who have only purchased from a narrow list of our products. I have tried building a report with CUST.dbf as the master table that links to INVOICE.dbf via the CUSTNUM field in "scan" mode; INVOICE.dbf the links to ITEMS.dbf via the ORDER field in "scan" mode (CUST and ITEMS do not have any fields in common). Then I have tried querying the Item field in ITEMS.dbf using the "not in the list" operator with wildcards to eliminate unwanted items. The problem is that it only filters out line items containing the offending items rather than filtering out customers connected with these line items. Provided that I have been clear enough as to what I^m after, does anybody know how to accomplish this?

[kfleming]

Sort and group by CUSTNUM and then use just a CUSTNUM footer in the report.__It will print each customer who has ordered one the items that are included in the query.____Kathleen__R&R Support

[simulacra]

That would get it if I were only trying to filter for a positive. The problem is, I need to filter for a negative as well; I need to return a list of all customers who have purchased products within a given list but have not purchased anything outside of that list. On a separate forum, someone posted the following:____"That is kind of a tough one in R&R because the MOM Items database that you pull from lists each individual item as a separate record. I think that what if would do is create a series of IIF statements that have specific numeric values for each item. Here is an example:____IIF(items->item = "X",-1, IIF(items->item = "Y",-1, IIF(items->item = "Z",-1,1000)))____I didn^t check the syntax, so it may not be perfect. Basically, what you get is if the item is equal to X, Y, or Z, a value of -1 is returned. If the item is equal to any other value 1000 is returned. After that, you would create a total field to sum the returned values. Finally, for the query, the criteria would to include any orders where the total field sum is less than 0."______Does this seem like a reasonable solution, or is there something simpler or more effective that would accomplish my ends? Thanks.

[kfleming]

You could create two conditional pre-processed totals.____Each would be a count of item that resets by customer.__ABC would use The condition :__inlist(item,"A","B","C") >0__You need to set the processed of this total to pre-processed.____This one will count any A B or C line items that were purchased by the customer.____Then you have a second similar total.__NOTABC will have the condition__inlist(item,"A","B","C") =0____This one counts all other products.____To see customers who have only purchased an A B or C you would query for ABC is greater than 0 and NOTABC is equal to 0.____This gives you all customers whose only purchase has been an A B or C.______Kathleen__R&R Support