>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:
>
Hi Cathy:

Thanks a lot. I got going on this with the updated SQL statement you so kindly provided me. On viewing the results, I can see what is really happening now.

TableA has the health number field named as PHealthNum, whereas TablesB, C and D have it as HealthNum. don't really know why I did that. However, the outcome is that the ORDER BY clause on PHealthNum is being flagged as an error by VFP.

Here's my actual code:

SELECT '1' AS SortKey, 'Family History' AS Group, ;
    PFullName AS Extra1, PAddress1 AS Extra2, PAddress2 AS Extra3, ;
    PCity as Extra4, PProvince as Extra5, PPostal as Extra6,;
    PPhone as Extra7, Marital as Extra8, Gender as Extra9,;
    ChartNum as Extra10, PatRefernc as Extra11,;
    BirthDate AS Date1, Initvisit AS Date2, GenAssess as Date3,;
    OnCpp as Date4,;
    PHealthNum, PersonData AS Descr FROM PatSummary; 
  UNION ALL SELECT '2' AS SortKey, 'Past History' AS Group, ;
    PFullName AS Extra1, PAddress1 AS Extra2, PAddress2 AS Extra3, ;
    PCity as Extra4, PProvince as Extra5, PPostal as Extra6,;
    PPhone as Extra7, Marital as Extra8, Gender as Extra9,;
    ChartNum as Extra10, PatRefernc as Extra11,;
    BirthDate AS Date1, Initvisit AS Date2, GenAssess as Date3,;
    OnCpp as Date4,;
    PHealthNum, PastHist AS Descr FROM PatSummary;
  UNION ALL SELECT '3' AS SortKey, 'Allergies' AS Group, ;
    PFullName AS Extra1, PAddress1 AS Extra2, PAddress2 AS Extra3, ;
    PCity as Extra4, PProvince as Extra5, PPostal as Extra6,;
    PPhone as Extra7, Marital as Extra8, Gender as Extra9,;
    ChartNum as Extra10, PatRefernc as Extra11,;
    BirthDate AS Date1, Initvisit AS Date2, GenAssess as Date3,;
    OnCpp as Date4,;
    PHealthNum, Allergies AS Descr FROM PatSummary; && TableA
  UNION ALL SELECT '4' AS SortKey, 'Conditions' AS Group, ;
    '' AS Extra1, '' AS Extra2, '' AS Extra3, ;
    '' as Extra4, '' as Extra5, '' as Extra6,;
    0 as Extra7, '' as Extra8, '' as Extra9,;
    '' as Extra10, OGReferenc as Extra11,;
    RecordDate AS Date1, ResolvDate AS Date2, {} as Date3, {} as Date4,;
    HealthNum , HealthCond AS Descr FROM OnGoingHealth; && TableB
  UNION ALL SELECT '5' AS SortKey, 'Regimen' AS Group, ;
    '' AS Extra1, '' AS Extra2, '' AS Extra3, ;
    '' as Extra4, '' as Extra5, '' as Extra6,;
    0 as Extra7, '' as Extra8, '' as Extra9,;
    '' as Extra10, TReference as Extra11,;
    StartDate AS Date1, EndDate AS Date2, {} as Date3, {} as Date4,;
    HealthNum, Regimen AS Descr FROM Treatment; && TableC 
  UNION ALL SELECT '6' AS SortKey, 'Consultations' AS Group, ;
    '' AS Extra1, '' AS Extra2, '' AS Extra3, ;
    '' as Extra4, '' as Extra5, '' as Extra6,;
    0 as Extra7, '' as Extra8, '' as Extra9,;
    '' as Extra10, CReference as Extra11,;
    Daterefer AS Date1, {} AS Date2, {} as Date3, {} as Date4,;
    HealthNum, Specialist AS Descr FROM Consultants; &&TableD 
ORDER BY  PHealthNum, SortKey ;
INTO CURSOR TmpData

Why is the error message being flagged? Thanks Cathy.