Two subforms - joins in query

[Natalie]

Hello - I have set up a database that shows a class - in
one subform /table it shows all the students in that class
and a second subform shows the tutors for that class -
this is fine! - they are linked via a field called
RCSgrpid and this field is in all 3 tables.

However - I am trying to create a query that grpups by
course and counts the number of students and the number of
tutors - if I just put the students table in the query it
groups by course and counts them no problem - but when I
add the tutors table it causes problems as the RCSgrpid
needs to be linked to a field in both tables and I have it
as 1 to many - so the count doesn't work properly (it
displays two records for each student and counts the no of
tutor plus students in each box!!)

I am sure it is to do with the way i have linked the
tables but i can't find another way to do it - any ideas?
Thanks!!!!

 

[Gerald Stanley]

The query should be something like

SELECT S1.RCSgrpid, S1.studentCount, T1.tutorCount
FROM
(SELECT RCSgrpId, Count(1) as studentCount FROM
{studentclasstable} GROUP BY RCSgrpId) S1,
(SELECT RCSgrpId, Count(1) as tutorCount FROM
{tutorclasstable} GROUP BY RCSgrpId) T1
WHERE S1.RCSgrpId = T1.RCSgrpId

You will have to replace {studentclasstable} and
{tutorclasstable} including {} with your own table names

Hope This Helps
Gerald Stanley MCSD

 

[Natalie]

Thanks for such a fast reply but the trouble is i don't
really know sql - i have designed the query with tables
showing at the top and if I check this out in sql view it
doesn't look like your code - should I just try replacing
it?? what do the s1 and t1 stand for?? sorry!!

 

[Gerald Stanley]

To set this as a Query, select a New Query but do not add
any tables. Then select SQL view and copy and paste the
sql from the previous message. Finally, change the table
names to those in your own database. You should then be
able to save it.

Alternatively, you could design two queries to do the
counting from the student and tutor tables, then design a
third query that combined the results from these together.

s1 and t1 are examples of Aliases - they have a number of
uses e.g. making explicit table references when the same
table is used more than once in a query or, as in this
case, where they refer to non-permanent queries.

Hope This Helps.
Gerald Stanley MCSD