combining 2 queries into one

[ploddinggaltn]

I have two tables, one for thousands of students and included in their record
is their school name....there are dozens of different schools. Then I have
another table with teachers and included in each of their records is their
school name. I want to get a count of the teachers and students for each
school but I can't figure out how, I can query to get each schools student
count and each schools teacher count but how can I obtain students plus
teachers for each school? any help is certainly appreciated. thank you.

 

[George Nicholson]

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.

 

[John W. Vinson]

I have two tables, one for thousands of students and included in their record
is their school name....there are dozens of different schools. Then I have
another table with teachers and included in each of their records is their
school name. I want to get a count of the teachers and students for each
school but I can't figure out how, I can query to get each schools student
count and each schools teacher count but how can I obtain students plus
teachers for each school? any help is certainly appreciated. thank you.

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]

 

[David W. Fenton]

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

I have two tables, one for thousands of students and included in
their record is their school name....there are dozens of different
schools. Then I have another table with teachers and included in
each of their records is their school name. I want to get a count
of the teachers and students for each school but I can't figure
out how, I can query to get each schools student count and each
schools teacher count but how can I obtain students plus teachers
for each school? any help is certainly appreciated. thank you.

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.

 

[ploddinggaltn]

Hi John,

Thanks for your offer for help. If you can get me started, I'm sure I can
figure out the rest.

From table "tblStudents" I need to have fields "SFirstName", "SLastName" and
"SchoolID" combined with "TFirstName", "TLastName" and "SchoolID" from the
table "tblTeacher"...I'd like to be able to have all the first names for
students and teachers in one column, the last name and school the same. I'm
not sure how to rename the columns so both the SFirstName and TFirstName are
in the same column. Should I just have buth both these groups into one table
to start? I didn't do that because there is some data that only pertains to
teachers and not students and viceversa. Thanks for your help, again if you
get me started I'm thinking I'll be OK iwht the others I need to do. I
appreciate your help, thank you.

 

[John W. Vinson]

Hi John,

Thanks for your offer for help. If you can get me started, I'm sure I can
figure out the rest.

From table "tblStudents" I need to have fields "SFirstName", "SLastName" and
"SchoolID" combined with "TFirstName", "TLastName" and "SchoolID" from the
table "tblTeacher"...I'd like to be able to have all the first names for
students and teachers in one column, the last name and school the same. I'm
not sure how to rename the columns so both the SFirstName and TFirstName are
in the same column. Should I just have buth both these groups into one table
to start? I didn't do that because there is some data that only pertains to
teachers and not students and viceversa. Thanks for your help, again if you
get me started I'm thinking I'll be OK iwht the others I need to do. I
appreciate your help, thank you.

This should do it for you. The names of the fields in the two tables can be
the same or different; the name or alias used in the first SELECT clause of
the UNION query will be what you see in the result:

SELECT SFirstName AS FirstName, SLastName AS LastName, SchoolID FROM
tblStudent
UNION ALL
SELECT TFirstName, TLastName, SchoolID FROM tblTeacher;

Your post is a bit confusing - surely you don't have both student and teacher
names in tblTeacher??

You may want to consider having a structure like:

People
PersonID
LastName
FirstName
<other biographical data pertaining to the person as a person, nothing about
being a teacher or student>

Teachers
PersonID <Primary Key *and* link to People.PersonID
<fields pertinant to this person's role as a teacher>

Students
PersonID <Primary Key *and* link to People.PersonID
<fields pertinant to this person's role as a student>


John W. Vinson [MVP]