469,275 Members | 1,835 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,275 developers. It's quick & easy.

How can I count unique values in a report?

sueb
379 256MB
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?
Sep 16 '11 #1

✓ answered by 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(<your date field>,"yyyymm") and add e.g. the [Patient MR] with the option "Count". That's all.

Nic;o)

7 10919
ADezii
8,800 Expert 8TB
Just subscribing for now...
Sep 16 '11 #2
nico5038
3,080 Expert 2GB
You need two ingredients for your reporting query:
1) The "GROUP BY" type of query to get a count.
2) The function: format(date,"yyyymm") 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)
Sep 16 '11 #3
sueb
379 256MB
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"?
Sep 16 '11 #4
sueb
379 256MB
(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!)
Sep 16 '11 #5
nico5038
3,080 Expert 2GB
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(<your date field>,"yyyymm") and add e.g. the [Patient MR] with the option "Count". That's all.

Nic;o)
Sep 16 '11 #6
sueb
379 256MB
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!
Sep 16 '11 #7
nico5038
3,080 Expert 2GB
Glad I could help, and you did a great job in making the query yourself :-)

Nic;o)
Sep 16 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Jo Goos | last post: by
1 post views Thread by jason | last post: by
3 posts views Thread by Mihir | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.