Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

How to create a report that counts unique values in one field

Copper Contributor

I have a database that lists clients, their info and who referred them.  I want to make a report that lists all the people who referred, and how many people they referred. I don’t know if it can be done with just queries, or if I need VBA, I’ve used visual basic before, but it’s been 12 years, so I don’t remember much. 

 

I have this table:

Client

Address

Referred_by

John Doe

23 aljdf

Scott

Juan Fulano

49 aljd

David

Jane Smith

857 alsdfj

Alison

Sam Jones

9348 askldfj

Scott

Sarah Smith

948 lsaj

Alison

Juana de Tal

9574 lsdkf

Scott

Frank Sinatra

4637afde

Alison

 

And I want this report:

Total Referrals:

Scott   3

David 1

Alison 3

4 Replies
best response confirmed by Soyeljefe77 (Copper Contributor)
Solution

@Soyeljefe77 

Create a Totals query by clicking the Sigma symbol on the Query Design ribbon

 

 

SELECT Referred_by, Count(Client) AS TotalReferrals
FROM YourTableName
GROUP BY Referred_by;

 

Use that query in your report

@isladogs 

Thank you for being willing to help.  All this does is change the name of the field Referred_by to TotalReferrals and puts a 1 instead of the name.

No. It creates the required output you asked for in the original post ... provided you use your actual table name in the query
Thank You! I was trying to incorporate it into a current query instead of creating a new one with just that script. Sorry I doubted :)
1 best response

Accepted Solutions
best response confirmed by Soyeljefe77 (Copper Contributor)
Solution

@Soyeljefe77 

Create a Totals query by clicking the Sigma symbol on the Query Design ribbon

 

 

SELECT Referred_by, Count(Client) AS TotalReferrals
FROM YourTableName
GROUP BY Referred_by;

 

Use that query in your report

View solution in original post