Suggests for Query with Sub-Query and Parameter

[asmetana]

I have a report which contains around 40 subreports. Each subreport uses the value of SAMPLES.U_SAMPLEID specified by the use and hard coded (one or several) TASKS.METHOD (s). I have recently discovered that there are times when there is not a one-to-one relationship between U_SAMPLEID and SAMPLE_ID. I need to report on the latest set of results, i.e. where SAMPLE_ID has the highest value for a given U_SAMPLEID. Ideally Crystal Reports 8.5 should create the query listed, however I have not found a way to get CR to create this query. If I manually edit the WHERE clause to add the sub-query and then change the parameter, the parameter in the sub-query does not change.

I have found a way to accomplish the same end result by grouping on SAMPLES.SAMPLE_ID DESCENDING and displaying only the first group of data when only one TASKS.METHOD is specified. When multiple TASKS.METHOD values are used to get a multicolumn subreport, this work around breaks down.

Any suggestions on how to attack this problem?

SAMPLES	        TASKS           RESULTS
------------	----------    -------------
| SAMPLE_ID |-->|SAMPLE_ID|-->|SAMPLE_ID   |
|       x   |   |     x   |   |       x    |
|       x   |   | TASK_ID |-->|TASK_ID     |
|U_SAMPLEID |   |     x   |   |       x    |
|       x   |   |     x   |   | RESULT     |
|       x   |   | METHOD  |   |       x    |
|       x   |   |     x   |   |       x    |
-------------	-----------   -------------


SELECT  
    	 RESULT
FROM
	SAMPLES, TASKS, RESULTS
WHERE
	SAMPLES.SAMPLE_ID = TASKS.SAMPLE_ID AND TASKS.SAMPLE_ID = RESULTS.SAMPLE_ID
	AND TASKS.TASK_ID = RESULTS.TASK_ID AND TASKS.METHOD = 'METHOD 1' AND
	SAMPLES.U_SAMPLEID = {?PARAM} AND  SAMPLES.SAMPLE_ID IN
		(SELECT MAX(SAMPLE_ID) 
		FROM SAMPLES
		WHERE U_SAMPLEID = {?PARAM})

 

[csm2]

I had a similar report to write. I coded the main query in SQL Designer, used the resulting recordset as i/p to Report Designer, where I used the sub-query as selection criteria.