Max date for record Set in "Add Command" function 2

[TakeCover328]

Ok, here is what I am trying to do. I usually use access to create subqueries that led to a master query. Each of these sub queries has information that leads to data that is used to create statistics for reviews. What I am trying to do is replicate this process in Crystal Reports. The first step that I am taking is create a sql statement in the "Add Command" function in Crystal that will pull the maximum date for each grouped set of information. I want this to be done prior to its placement in the database because this is only one of many sub-queries. So...

:::TABLE SAMPLE:::
Table Name: ES-SR:TaskAuditHistory
task-id;: status; : Group; : Individual; : Create-Date
---------------------------------------------------------
12345 : Assigned : AccessCtrl: Null : 4/1/2008
12345 : Completed: SvcDsk : Bob : 4/2/2008
12345 : Assigned : SiteSuppor: Jack : 3/3/2008
54321 : Assigned : AccessCtrl: Joe : 4/1/2008
54321 : Assigned : AccessCtrl: Jim : 4/1/2008
---------------------------------------------------------

Ok, so what I want is to see the Max Date for each Task ID assigned to Access Control.

Here is what I have for the command:

SELECT
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date as Assigned-Date
FROM
ES-SR:TaskAuditHistory
WHERE
Status = 'Assigned' and
Assigned-Group = 'Access Control' and
Assigned-Individual <> ''
GROUP BY
Task-ID,
Status,
Assigned-Group,
Assigned-Individual,
Create-Date

...

I have attempted to add in to the WHERE clause:

Create-Date IN
(SELECT
MAX(Create-Date)
FROM ES-SR:TaskAuditHistory
GROUP BY Task-ID, Status, Assigned-Group)

That didnt work so then I tried:

Create-Date IN (Select Max(Create-Date) FROM ES-SR:TaskAuditHistory as F where F.Create-Date = ES-SR:TaskAuditHistory.Create-Date GROUP BY Task-ID, Status, Assigned-Group)

Neither of them worked, I get the follow error:

Failed to open a rowset. Details: 420river]Expectied lexical element not found: <Identifier>

I am using Crystal Reports 10; I am connecting through an AR System ODBC Source.

To me, it seems as if the ad hoc query is not running so it has nothing to compare the create date to? Thoughts?

 

[lbass]

If the two components each work separately, then the only issue left is that for some reason:

Create-Date = (select max(TAH.create-date)
from ES-SR:TaskAuditHistory TAH
where TAH.Status = 'Assigned'
and TAH.Assigned-Group = 'Access Control'
and TAH.Assigned-Individual <> ''
and TAH.Task-ID = ES-SR:TaskAuditHistory.Task-id)

...doesn't work. Maybe try replacing '=' with 'in'. Not sure why that would make a difference though.

-LB