report the # of times a name appears in a field

[rankhornjp]

I have a datatbase where I keep all my customer information. One field is for referrals (who referred that customer to me). I want to know how to have the report list the unique names in the referral field and then list the number of times each name appears. Is this possible?



If I know the names I can do a text box with "=count(IIF([referral]="Joe",1))" but I don't always know all the names.

[pbaldy]

Sure; I'd use a subquery/subreport. It would be a totals query that grouped on the referral field and counted the same field (actually probably any field, or "*").

[rankhornjp]

That sorta works. now I get

Joe 5
Joe 5
Joe 5
Joe 5
Joe 5
And in a seperate box (on a seperate page)
Tim 3
Tim 3
Tim 3
ETC......

I would like it to all be on the same page and in the same box and only listed once.
Joe 5
Tim 3
Larry 12
etc......

[pbaldy]

Doesn't look like the SQL is correct for the totals query. What is it?

[rankhornjp]

I did a subreport from the original query. Now I understand you are suggesting a subquery as well? How do I do that?

Thanks

[pbaldy]

Not a subquery, but another query that the subreport is based on. It would be a totals query, like:

SELECT referral, Count(*) As HowMany
FROM TableName
GROUP BY referral

You may need a criteria to limit to the same set of records the report is based on.

[rankhornjp]

Ok that works now. However, now I get multiple pages displaying all the same information (page # varies as to the number of results the main query returns). i.e. if the main query returns 5 results, I get 5 pages....

[pbaldy]

Can you post the db here?

[rankhornjp]

not really, most of the info is private. I can post a screen shot of the query or the SQL......

[rpeare]

if your subreport is linked to your report do not have ANYTHING in the detail section of the subreport. Put your person name in the PERSONNAME FOOTER, and a formula in another field that is = count(Personname).

[rankhornjp]

How do i get the multiple pages to go away?

[rpeare]

Have you tried shrinking your detail section of your report down to 0, or if it already is 0 playing with your margins, it's kind of impossible to tell what you mean by 'the multiple pages'

[rankhornjp]

Even with the report blank, the report will show 1 page for every result in the query.

[rpeare]

how can it be a blank report and have a record for every item, a blank report would have a null dataset

[rankhornjp]

ok.... If I create a report from the query, then delete all the text boxes from the report. The report will print a number of blank pages. The number of pages = the number of results from the query.



ETA: I just added a text box with "=count(IIF([pRace]="W",1))" and then used the Hide Duplicates property. Now it shows the text on the first page followed by 6 empty pages(there are 14 query results), instead of the same text on 14 pages.