How do I NOT count duplicate records in a report?

[PureGrace]

I'm working on a research database and I'm creating reports on researchers. The first report I want to calculate the number of articles published, the number of projects they have worked on and the number of grants they have.

However, they want the records sorted by class year (the year the project started) and many of the researchers have multiple projects. So everytime it does the final sums it counts some researchers two or three times.

The other problem I'm having is that when I do queries with more detailed information about the researchers (article titles, publications) it will also show multiple results for each researcher.

Example
Name Class Year Article Name Publication
Joe Smith 2000 There was a dog Scientific Magazine
Joe Smith 2000 There was a cat Cat Magazine
Joe Smith 2002 There was a dog Scientific Magazine
Joe Smith 2002 There was a cat Cat Magazine

Is there anyway to narrow down this entries in the report? Otherwise I'm counting each article and publication twice and each researcher 4 times in this example.

Thanks in advance for all your help!!!

 

[dhookom]

There are a number of methods. You can use subreports for the details on main report. You can also create totals query to generate the exact sums/counts you want and then join them in to the record source of your report.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[snayjay]

Go to the query that is generating your report. View it in design mode. From the menu click View--> Properties. Change Unique Values to 'Yes'. See what that does.