unduplicating queries

[Alicia]

I have a db that I am running a query on. I have a table
of schools and a table of students. There are several
students taking classes in ONE school. What I am trying to
do is create a query that will say how many students are
in the school and eliminate all the duplicate. The records
are ONE school, ONE student with MULTIPLE courses. I need
to know how many students are taking classes in the
school, but I do not need to know how many classes they
are taking.

I know there is a way to do this. I have already gone into
property and made the unique records "yes" and this does
not seem to help.

Whatever you can help me with I would greatly appreciate.

 

[[MVP] S. Clark]

Select Count(StudentID) from tblRegistration
Group By StudentID

The Group By clause will eliminate the dups.

--
HTH,

Steve Clark, Access MVP
FMS, Inc.
Professional Solutions Group
http://www.FMSInc.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Is your Access database too slow?
Are you ready to upgrade to SQL Server?
Contact us for optimization and/or upsizing!
http://www.FMSInc.com/consulting
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[John Spencer (MVP)]

What version of Access? The following might work in Access 97

SELECT Count(A.Student) AS CountOfExpr1
FROM [SELECT Distinct Student FROM SomeTable]. AS A;

Note the use of SQUARE Brackets and the DOT at the end.

You could accomplish this in TWO queries in all versions of Access.

QueryOne
SELECT Distinct Student FROM SomeTable

QueryTwo uses query one
SELECT Count(*) FROM QueryOne

It might also be possible to get the correct results other ways, depending on
your table structure.

Do you have a separate table for students, Another for courses, and another for
Courses being taken by Students? If so, you could query the students table.

SELECT Count(StudentTable.StudentID) as CountEm
FROM StudentTable
WHERE StudentID In
(Select t.StudentID FROM StudentsInCourse as T)

This would return one record for each StudentTable.StudentID where the Student
was taking at least one course.