I'm not exactly sure I'm following what you are asking, but I'll give it a try. I think you are saying that table A has Health#, FamilyHistory, PastHistory, and Allergies and it's one record per Health#. Table B has Health# and Condition and can contain multiple records for a Health#. Table C has Health# and Regiman and can contain multiple records for a Health#. Table D has Health# and Consultants and can contain multiple records. You want a 2-column report that prints all this information on one page for each Health#.

Well .. if I got that all right .. here's how I'd approach this:

Create a cursor with the following SQL SELECT:

SELECT '1' AS SortKey, 'Family History' AS Group, ;
    HealthNo, FamHist AS Descr FROM TableA ;
  UNION SELECT '2' AS SortKey, 'Past History' AS Group, ;
    HealthNo, PastHist AS Descr FROM TableA ;
  UNION SELECT '3' AS SortKey, 'Allergies' AS Group, ;
    HealthNo, Allergy AS Descr FROM TableA ;
  UNION SELECT '4' AS SortKey, 'Conditions' AS Group, ;
    HealthNo, Condition AS Descr FROM TableB ;
  UNION SELECT '5' AS SortKey, 'Regimans' AS Group, ;
    HealthNo, Regiman AS Descr FROM TableC ;
  UNION SELECT '6' AS SortKey, 'Consultations' AS Group, ;
    HealthNo, Consult AS Descr FROM TableD ;
ORDER BY HealthNo, SortKey ;
INTO CURSOR TmpData

Now create a report and set the number of columns to 2 in the Page Setup dialog. Create two Data Groups on the report. The first one should have an expression of HealthNo and mark it to start on a new page. The second one should have the expression of SortKey.

In the Data Group Header for the HealthNo band, you can put any fields that are related to the patient. Note .. you'll have to add these fields to the SELECT statement because I didn't include anything like Name.

In the Data Group Header of the SortKey band, put a Field object with the expression of Group. This will print the heading of each section for you.

In the Detail band, put one field that has an expression of Descr. Mark this as Stretch with overflow.


I hope I understood the problem and gave you enough information to help you solve it!!

Cathy