Hi there, I have been on this forum a number of times before and have always received satisfactory responses for my questions.  However, I have always struggled to use crystal for running subqueries i.e. having one main report running on the results of a sub query. Below is what, I am trying to achieve is:

SELECT DISTINCT A. physician_name, A.account_number, A.date_of_service, A.patient_type, A.code

FROM error_fact A

WHERE C.account_number IN

(SELECT account_number FROM error_fact C

WHERE C.date_of_service >= "01/01/2010" AND C.patient_type = “O” AND C.code IN ("93307","93325","93320")))

In the aforementioned, I can’t apply the filter in the main query because if I do I will see accounts and their related information with codes in the condition (this is what I want) and not see other codes (this is not what I want) i.e. if an account has 6 codes, out of which 3 are the ones in the filter condition, I want to see all 6 and not just 3 which match the condition. That is why, I filter out accounts satisfying the condition and then query out all the information (everything I need on an account) in the main query.

For clarity, please see the example below

Account      Codes

A                   93307

                      93325

                      98789

B                   10000

If I apply filter in the main query, my result looks like

A       93307

          93325

And it does not show ‘98789’ (this is not acceptable), however it only shows account A and not B (this is desired)    

I want to know if this is possible in Crystal by means of passing the accounts from a sub-query to main query. Also, please notice that it will be a list of accounts that will have to go from sub-query to main query.

P.S. Each account has multiple codes.