I have two access tables that I am joining. In the opty table I want all records that meet the WHERE clause and in the activity table I want any/all that meet the join clause. The opty table has a one to many relation to the activity table. The join works correctly, it is the display that I am having problems with. I want the output display as an opty then under that all the activities associated to the opty in ASC date order. My problem is I have a memo field in the opty table I want to display but if I put a GROUP BY clause in the sql then I only get part of the memo field (I know why this is happening). So, I tried to leave out the GROUP BY in the sql and use the OUTPUT GROUP BY in the display section. This works except if there are numerous activities associated to an opportunity then the opportunity (sometimes) will be displayed twice with different activities listed. Can some please help.

If I use the the ORDER BY, and put CDATE(acty_begin_dt), I get a NULL error. Which, I assume is because some of the opty's may not have an activity associated to it.

Below is the code I am using and it works fine except, the activities are not in the correct order. (I am using this query in a cold fusion application using access db)
Thanks


SELECT opty_row_id, opty_name, opty_created_by, opty_stage, opty_type, acty_type, acty_created_by, acty_status, acty_opty_row_id, acty_desc, acty_comments, opty_primary, opty_create_dt, opty_close_dt, opty_est_value, opty_desc, acty_row_id, acty_begin_dt, acty_end_dt
FROM opty LEFT JOIN activity ON (opty.opty_row_id = activity.acty_opty_row_id)
WHERE (Trim(opty_primary) = '#form.rpt_user_name#' AND (CDate(opty_create_dt) between #get_first# AND #get_last#)) OR (Trim(opty_primary) = '#form.rpt_user_name#' AND (CDate(opty_create_dt) < #past_date# AND (Trim(opty_stage = '01 - Prospecting') OR Trim(opty_stage = '04 - Opportunity') OR Trim(opty_stage = '03 - Qualification'))))
GROUP BY opty_row_id, acty_row_id, acty_begin_dt, opty_name, opty_created_by, opty_stage, opty_type, acty_type, acty_created_by, acty_status, acty_opty_row_id, opty_primary, opty_create_dt, opty_close_dt, opty_est_value, opty_desc, acty_end_dt, acty_desc, acty_comments
ORDER BY CDate(opty_create_dt) Desc

Typically this is done using two queries and a subform or subreport that join to the main form/report. Even if not using subform/subreport then two queries is still a better deal with the second being run against the main data only (I.E. all the multies to the one).