I have created a BAQ that shows top customers based on order amounts. I now would like the BAQ to show me top customers but combine customers that have the same national account. The problem I run into is there are some customers that have national accounts and some that don’t have national accounts. Any assistance would be greatly apricated. My query diagram starts at orderhed then customer then RLS parent.
Welcome, Mark!
It sounds like you want to use the Relationship Tables (probably RlsParent) table to determine which customers have a national account. The RlsParent has two keys you can use: CustNum and TopCustNum. If you create a subQuery of these relationships first, you can add a calculated field to become the grouping key. If a customer has a national account, the Top Parent CustNum will be the value, otherwise the customer will be the value of this field.
Once you have this subfile, you link to it as if it was the Customer table and do the same top X logic you have been doing.
Others may have alternate ideas. Check the list to see how to do outer joins, as this is important when record links are optional!
2 Likes