Need help with Reports

[Aditya Lele via AccessMonster.com]

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.

 

[PC Datasheet]

You need tables like this:

TblFaculty
FacultyID
DeptartmentID 'From TblDepartment
RankID 'From TblRank Instructor, Professor, etc
DateHired
FirstName
MI
LastName

TblFacultyGrant
FacultyGrantID
FacultyID
GrantSource
GrantAmount

You could get the number of faculty hired by rank or department from a query
based on TblFaculty and an appropriate criteria on the DateHired field.

You could get a list of grants for each faculty member from a query based on
both tables.

 

[Aditya Lele via AccessMonster.com]

Appreciate the response by PCDatasheet.

I am able to get the no.of faculty hired by making a query. But I have not
been able to include this query in my report because of the problem stated
in the original posting. Shall appreciate if anyone could specifically
answer the question. I have proper tables and am able to create queries and
generate reports. But would like to know the reason for the erro prompt and
a way to get around it.

Thanks.

 

[PC Datasheet]

You don't have the proper tables if you are having this problem! Try the
tables I suggested and buile your query only on TblFaculty.