Assuming: 2 tables (tblTeachers, tblStudents), and that each have a field
named SchoolName.
The following would give you 2 records per school, with CountType (Teachers
or Students), SchoolName and RecCount.

(This can't be done in the query designer grid, it has to be done in the
query designer's SQL view).
SELECT DISTINCT "Teachers" as CountType, SchoolName, Count([SchoolName]) AS
RecCount
FROM [tblTeachers] GROUP BY [SchoolName]
UNION SELECT DISTINCT "Students" as CountType, SchoolName,
Count([SchoolName]) AS RecCount
FROM [tblStudents] GROUP BY [SchoolName];

Save the above UNION query as qryCountTypeUNION.

Then use it as the basis for a CROSS-TAB query:
(This can be done in the query designer grid:)
   SchoolName, GroupBy, RowHeading
    CountType, GroupBy, ColumnHeading
    HeadCount: RecType,  Sum, Value
    Total Headcount:RecType, Sum, RowHeading

The Crosstab should have SQL similar to:

TRANSFORM Sum(RecCount) AS HeadCount
SELECT SchoolName, Sum(RecCount) AS [Total Headcount]
FROM qryCountTypeUNION
GROUP BY SchoolName
PIVOT CountType;

The cross tab should result in a single record for each school with 3
columns for Teacher, Students and Total Headcounts.

See the online help for UNION. It's pretty clear, and it's the solution you
need. If you need help post back with the relevant fieldnames in your two
tables.

            John W. Vinson [MVP]

=?Utf-8?B?cGxvZGRpbmdnYWx0bg==?=

Two observations:

1. I never put people in separate tables based on the functions
those people server. Instead, all people are in the same tables, and
I use some other method for determining what function they serve
(this might be a field in tblPerson if it's a very simple app; it is
more likely a many-to-many join table so that a person can have more
than one function).

2. if all you need is the count, just check
CurrentDB.TableDefs("tblTeacher").RecordCount and
CurrentDB.TableDefs("tblStudent").Recordcount and add them to
gether. If you need to filter out certain records, then use:

 SELECT Count(*) As TeacherCount FROM tblTeacher
 WHERE [conditions here]

and use DAO to return that number. Then do the same with tblStudent
and add them together.

The only reason you might UNION the two tables is if you need to
display the actual data. You don't say you need to do that -- you
only mention counting them, so I don't see any reason to do a UNION
at all.

But perhaps I've not read your post carefully.