Report based on Multiple queries

[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.

 

[Allen Browne]

There are several solutions for this. Here's one that does not require you
do anything to the source query, or use slow domain aggregate functions.

If you have created a group header section for the person (probably a
FacultyID field) through the Sorting And Grouping box (View menu), you could
add a text box to this group header, and give it these properties:
Name txtFacultyCount
Control Source =1
Format General Number
Running Sum Over All
Visible False

Then in the Report Footer section, place a text box with these properties:
ControlSource =[txtFacultyCount]
Format General Number

Each time the reports comes to a new faculty member header, it will add 1 to
the hidden text box, and then the one in the report footer shows the total.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in message
news:[email protected]...