count unique records for report

[creolehottie]

Hello-

I am new to this site. Below is what I'm trying to build into my database:

I have a table with a list as follows:
ID ClusterName
1 CA_001
2 CA-001
3 CA_002
4 CA_002
5 CA_003

etc....

I want to count the total number of Unique records from this table and display this value at the top of a report. So basically the question is..."How many unique clusters are there?"

The next step would be to list each unique cluster and have a detail line listing of all the people falling into that cluster.

I.e. of Ideal Report Layout:

Total Number of Clusters: Value above

CA_001- 2 people
ID 1
ID 2

CA_002- 2 people
ID 3
ID 4

CA_003- 1 person
ID 5

If any one has a clue on how to begin building this report into the database I would really appreciate your help. Thank you.

 

[pbaldy]

It's actually fairly simple. Use the report wizard (based either directly on the table or a query that returns the records you want), and when it asks you if you want to add grouping levels, make ClusterName one. Then when it's done, add a field in the group header like:

=Count(ID)

 

[creolehottie]

It's actually fairly simple. Use the report wizard (based either directly on the table or a query that returns the records you want), and when it asks you if you want to add grouping levels, make ClusterName one. Then when it's done, add a field in the group header like:

=Count(ID)

Thanks for the reply, however I do not think it is that simple. If I use your advice above it would return 5 clusters when there is really only 3 unique clusters.

Do I need to reassign the primary key to ClusterName? Would the =Count(ClusterName) function distinguish the unique records?

 

[pbaldy]

Did you try the wizard as I suggested?

 

[pbaldy]

If you had, you'd have a report like this, which looks like what you asked for:

 

[Steve5]

query

Create a query with a count coulnm and click on the [sum] button on your tool bar.

It will list the unique records while telling you how many you have for each unique record.

 

[creolehottie]

thanks

thanks for your help pbaldy and steve5! Both methods give me the right results..


The report is exactly what i want...thanks again.