Sure. Just change the number used in the AS SORTKEY portion of the SELECT statement. For conditions use "1", for Medications use "2", and so on.

Cathy

>Hi Cathy:
>
>Is there a way of printing the results of the select statement in the following order:
>
>1. Conditions
>2. Medications
>3. Consultations
>4. Family History
>5. Past History
>6. Allergies
>
>Thanks.
>
>
>
>>Basically, you need to make each SELECT stmt have the same list of fields for each table so they can be UNIONed together. Try adjusting the SQL Statement to something like the following:
>>
>>

>>SELECT '1' AS SortKey, 'Family History' AS Group, ;
>>    demox AS Extra1, demoy AS Extra2, demoz AS Extra3, ;
>>    SomeDate AS Date1, SomeDate AS Date2, ;
>>    HealthNo, FamHist AS Descr, FROM TableA ;
>>  UNION SELECT '2' AS SortKey, 'Past History' AS Group, ;
>>    demox AS Extra1, demoy AS Extra2, demoz AS Extra3, ;
>>    SomeDate AS Date1, SomeDate AS Date2, ;
>>    HealthNo, PastHist AS Descr FROM TableA ;
>>  UNION SELECT '3' AS SortKey, 'Allergies' AS Group, ;
>>    demox AS Extra1, demoy AS Extra2, demoz AS Extra3, ;
>>    SomeDate AS Date1, SomeDate AS Date2, ;
>>    HealthNo, Allergy AS Descr FROM TableA ;
>>  UNION SELECT '4' AS SortKey, 'Conditions' AS Group, ;
>>    '' AS Extra1, '' AS Extra2, '' AS Extra3, ;
>>    RecordDate AS Date1, ResolvedDate AS Date2, ;
>>    HealthNo, Condition AS Descr FROM TableB ;
>>  UNION SELECT '5' AS SortKey, 'Regimans' AS Group, ;
>>    '' AS Extra1, '' AS Extra2, '' AS Extra3, ;
>>    StateDate AS Date1, EndDate AS Date2, ;
>>    HealthNo, Regiman AS Descr FROM TableC ;
>>  UNION SELECT '6' AS SortKey, 'Consultations' AS Group, ;
>>    '' AS Extra1, '' AS Extra2, '' AS Extra3, ;
>>    ReferDate AS Date1, {} AS Date2, ;
>>    HealthNo, Consult AS Descr FROM TableD ;
>>ORDER BY HealthNo, SortKey ;
>>INTO CURSOR TmpData
>>

>>