Crystal Report Selection Question

Question

If I had a single table of items ordered (let's call the table "items"), and there was a column within the table that tied the items in the same order together (order_id), how would I select all orders that contained a certain "key" item? In other words, I want to select orders that contain the "key" item and I want to see all the items with in that order, not just the "key" item. For example, I want to select all grocery orders (and all the items within) where the person at least ordered "apples".

Answer 1

You could probably (although I haven't tested it):

1. Add your table twice (so you will have two aliases pointing to the same table: table_1, table_2), joining on order_id
2. In your selection formula, use {table_1.key} = "apples"
3. Add a group on {table_1.order_id}
4. Use fields from table_2 for the detail section

Alternatively, add the table once, select on {table.key} = "apples" and group by {table.order_id}. Add a subreport in the group header linked by {table.order_id} to display the items. This will be slower, but it will definitely give you what you want.