How can I count unique values in a report?

[sueb]

I have a very simple, flat database [Contacts] that has, basically, a date of patient contact [Contact Date], the identification of the social worker who contacted the patient, and the medical record # of the patient [Patient MR] (along with some notes about the contact).

I want to create a report that displays a line for each month (of a specified period) containing the month/year and the count of patients that were contacted (as opposed to the number of contacts made; a patient might be contacted more than once in a single month).

I know how to get all this stuff except for the unique count. How do I do that?

[ADezii]

Just subscribing for now...

[nico5038]

You need two ingredients for your reporting query:
1) The "GROUP BY" type of query to get a count.
2) The function: format(date,"yy yymm") to get the (year)month counted.

There's a risk however that two contacts of the same patient with the same social worker are counted for one.

Just give it a try and report when you run into trouble.

Nic;o)

[sueb]

The thing about that is that I don't know what to put in the Value entry. I'm really only interested in date and unique patients, so what's the "value"?

[sueb]

(Oh, and just as an aside, "two contacts of the same patient with the same social worked counted for one" is exactly the result I'm going for!)

[nico5038]

The "Value" is only available when you use a "cross-table" query type.

Just select the "Group by" by pressing the "E" looking button for a regular SELECT query.

Next select the "GroupBy" option for the field "format(<yo ur date field>,"yyyymm" ) and add e.g. the [Patient MR] with the option "Count". That's all.

Nic;o)

[sueb]

Oh! See, I didn't even know the "GroupBy" and "Count" options were available except in a Crosstab query!

Thanks, nico5038! This is perfect now!

[nico5038]

Glad I could help, and you did a great job in making the query yourself :-)

Nic;o)