From: liz@paradoxcommunity.com   
      
   Craig,   
      
   The problem is probably that not all 5 rows are always involved.  Rather   
   than doing the five rows, why not one row with either:   
      
   a) a join to a criteria table in which the ClinicalCodes of interest are   
   added   
      
   OR   
      
   b) a single variable which is built to look like:  stCode = "ALI OR CVL"   
      
   ...?   
      
   Liz   
      
   "Craig"  wrote:   
   >Each patient has a table called Clininfo.db   
   >3 key fields and a field Called ClinicalCode (also keyed)   
   >Each patient can have anywhere from 0 to max # of choices available (36   
   I   
   >think) to put in each table   
   >   
   >I want to be able to query the ClinInfo table for how many patients have   
   1   
   >or more than 1 of these criteria.   
   >For example: asking the question  How many patients have had ALI and CVL   
   >(Arterial lines and Central Venous Lines). This is an "AND" Query.  I have   
   >arbitrarily put a limit on 5 clinical criteria  (for my own sanity).   
   >   
   >I have put together a manual query that works. It looks like this:   
   >   
   >Query   
   >ANSWER: :ktables:ClinInfoAnswer.db   
   >   
   >:ktables:Clininfo.DB | Status       | Year         | Unique#      |   
   >                               |  Check _join1 | Check _join2 | Check _join3   
   >|   
   >                               | _join1       | _join2       | _join3   
   >|   
   >                               | _join1       | _join2       | _join3   
   >|   
   >                               | _join1       | _join2       | _join3   
   >|   
   >                               | _join1       | _join2       | _join3   
   >|   
   >   
   >:ktables:Clininfo.DB | ClinicalCode |   
   >                               | ~st1              |   
   >                               | ~st2              |   
   >                               | ~st3              |   
   >                               | ~st4              |   
   >                               |  ~st5             |   
   >   
   >:ktables:Bioinfo.DB | Status | Year   | Unique# | HospitalNumber | FirstName   
   >|   
   >                                 | _join1 | _join2 | _join3  | Check   
   >| Check     |   
   >   
   >:ktables:Bioinfo.DB | LastName | Birthdate | Sex    |   
   >                               | Check      | Check     | Check  |   
   >   
   >:ktables:Medinfo.DB | Status | Year   | Unique# | AdmitDateTime |   
   >                                | _join1 | _join2 | _join3  | Check ~stwhen   
   >|   
   >   
   >EndQuery   
   >   
   >This Query actually works when I put data in instead of variables.   
   >In my code I would do a CASE  ENDCASE and have code with 5, 4, 3, 2 or 1   
   >query line(s) on the clininfo table depending on how many variables.   
   >I was thinking there must be an easier way to do this.   
   >Any ideas?   
   >Thanks,   
   >Craig Futterman   
   >   
   >   
   >   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)