Eric,
After you do your "tricky SQL", do one more pass at the data, something like this:
SELECT "MOVES " AS FieldType, Name, Sex, From, To, Date, ;
SPACE(5) AS RoomNum, SPACE(1) AS RoomSex, ;
< more stuff > ;
FROM MovesCursor ;
INTO CURSOR NewMoves
SELECT "VACANT" AS FieldType, SPACE(20) AS Name, SPACE(1) AS Sex, :
{} AS FromDate, {} AS ToDate, {} AS EffDate, ;
RoomNum, RoomSex ;
FROM VacantCursor ;
INTO CURSOR NewVacant
Then find a way to combine/append the two cursors (by building number?) so that the records come out in the order you want.
Put all fields on the report in the detail line with a Print When determined by the FieldType. This means that when FieldType is "MOVES " the name, sex, etc. show and when FieldType is "VACANT" the RoomNum and RoomSex fields show.
>The moves report has name, studentsex, from, to, date.
>
>The vacent report just has roomnum, roomsex
>
>Both are actully created with some pretty tricky sql so i dont think i could combine them.
>
>I guess Ill just have to leave it the way it is.