Skip to Content
0
Jun 27, 2019 at 07:50 AM

Using 'Select' to return only first occurrence of a field

416 Views Last edit Jul 02, 2019 at 11:40 AM 2 rev

Dear Users,

I am a Crystal "newbie" and I've got a question that I'm hoping someone can help me with. I've generated a report in which the fields have got a 1 to 1 relationship with the primary key (see pic1.png).

However when I include a new field - "Activity" which has a many to 1 relationship with the primary key it returns multiple entries for the same patient as you would expect (see pic2.png).

What I want to do is have the "Activity" field

return only the first occurrence. I've tried using the "Group By" functionality with no success, my biggest problem is that I can't get the Formula Editor to recognise my syntax (see bottom of page for example). I use Crystal 2011.

Any help would be greatly appreciated. Thanks -

SELECT
     XXX.'YYY',
FROM
     'XXX' XXX
GROUP BY
     XXX.'YYY'