Count unique records in a field?

[notfound]

Hello all,

My database tracks therapy sessions with clients. I am trying to generate summary statistics for each month.

The important fields here in my table of sessions are:
SessionType (Assessment or Therapy)
Attendance (Attended; CancelledByClient; CancelledByMe; DidNotAttend)

The stats I need are counts of attendance codes for each of these:

Assessment
First therapy
Ongoing therapy

For most of these, I am using summed IIF expressions like:

AssmtATT: IIf([Sessions]![SessionType]="Assessment",IIf([Sessions]![Attendance]="Attended",1,0),0)

But how can I count _first_ therapy cases? I need something which checks if there are any other SessionType=Therapy for that client, and if not, it counts as 1.

I would like to avoid creating a new SessionType,

Thanks for your time,
nf

 

[ajetrumpet]

unique records are counted like this:

SELECT DISTINCT [field] from TABLE

if you include more than one field in a DISTINCT query, the records really aren't DISTINCT, so to speak.

 

[notfound]

Thanks for the reply ajetrumpet,

I realised what I was after needed a different approach - I worked out something like:
IIF ([TotalTherapySessions]=1,"FirstTherapy","ContinuedTherapy")

TotalTherapySessions is always changing for each client, but my monthly stats needs a column counting first therapy sessions. So a new SessionType was the easiest.

I'm sure there are other ways but that's easiest for now.

thanks,
nf

 

[The_Doc_Man]

Maybe you need to look at this in a different way. Look at CROSSTAB queries as a way to get your counts. If you can handle a long, tabular report, you should be able to get everything you wanted from that.

 

[gemma-the-husky]

i dont think you can do this without more information

one way would be to have a visit counter in the table
so a new case gets a count of 1, and later ones are incremented

otherwise, just have a newcase flag - but the first option gives more info

--------
the problem is you cant idenitfy a new case otherwise

since if you examine cases in a date range, then the earliest visit in that range, may still not be the first treatment for the patient.