Forum Discussion
How to create a report that counts unique values in one field
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
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
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
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
