Grouping question

[rjm13]

Hi All,

I am trying to construct a query that gives a clients first enrollment into a program, And what I need for results are the clients ID , date of first enrollment, Program, and the unique program enrollment identifier.

The query I have constructed is as follows:

SELECT dbo_ai_prog.tc_id, dbo_program.descript, Min(dbo_ai_prog.start_dt) AS MinOfstart_dt
FROM dbo_program INNER JOIN dbo_ai_prog ON dbo_program.prog_id = dbo_ai_prog.program
GROUP BY dbo_ai_prog.tc_id, dbo_program.descript
HAVING (((dbo_program.descript) Like "HR (RW)*"));

The problem is that if I try to add in the program enrollment unique identifier, it is making me group on it, the message says it has to be an aggregate function. I need to be able to just add in the program enrollment unique identifier and have it be part of the row but not group on it because then the query rturns all rows, because the field is a unique identifier.

Can anyone help me on this?

Thanks,

RJ

 

[Crowley16]

try using a subquery to select the id...

select blah, (select descript from dboProg WHERE id=id), blah...

--------------------
Procrastinate Now!