Hi!All

I am trying to create a report which tracks faculty progress.So the kind of
data I am reporting at a departmental level is the yearly funding received,
yearly grants generated etc. I am getting this data from a query and using
the sum options within the report to get an aggregate for each department.
Key fact here is that one faculty might have generated 10 grants and so at
a detailed level there would be 10 instances identifying this faculty
within a particular department.

I also need to report no. of faculty hired by each department. My problem
is because of the fact stated above,
using count(facultydentifier) sums over the multiple instances of the
faculty. So for the faculty generating 10 grants, the report tells me that
10 people were hired, whereas actually 1 was hired.

I read on the web on how to create reports based on multpiple queries. When
I try to do that I get the error "You have chosen fields from record
sources which the wizard can't connect. You may have chosen fields from a
table and query based on that table. If so try choosing fields only from
the query or only from the table". Indeed my second query uses one of the
table used in the first one. But I don't know how to get around it.
I tried doing the count function in the first query, but that is not right,
since all other records are at a detailed level while the count is at an
aggregate level.

Would highly appreciate any pointers and help.
Thanks.