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
>Hi Cathay:
>
>Thank you for your kind response.
>
>>I think you are saying that table A has Health#, FamilyHistory, PastHistory, and Allergies and it's one record per Health#.
>Yes, and some more fields with demographic data and date fields. FamilyHistory, PastHistory and Allergies are all memo fields that can be updated.
>
>Do I need to do the same for each field in TableA as you've shown in your example?
>
>>Table B has Health# and Condition and can contain multiple records for a Health#.
>Yes, and has RecordDate and ResolvedDate fields. Here is where each condition is recorded and the date resolved is also identified.
>
>>Table C has Health# and Regiman and can contain multiple records for a Health#.
>Yes, and includes fields for StartDate and EndDate.
>
>>Table D has Health# and Consultants and can contain multiple records.
>Yes, and includes a field to show when the referral was made.
>
>>You want a 2-column report that prints all this information on one page for each Health#.
>
>Yes. The user will make the selection.
>
>>I hope I understood the problem and gave you enough information to help you solve it!!
>
>Ofcourse you have Cathy and it is appreciated.