Counting Confusion

  • Thread starter Thread starter Dazed And Confused
  • Start date Start date
D

Dazed And Confused

I know I should know the answer to this, but I don't, so at the risk
of sounding stupid, I hope someone can please set me straight once and
for all.
*
I am writing a query against table tblenrollment. I am querying two
fields: student_id and enrollment_date. I want to know the number of
people enrolled on or after 01/01/08.
*
In design view I have the two fields side by side. In the "Total"
field for the student_id I
specifiy Count. For the enrollment_date in the total field I specify
"Where" and in the criteria field, I type >=01/01/2008. The answer I
get back is 257.
*
If I try the exact same query but instead of Count in the student_id
Total field, I specifiy Group By and under the query properties I
specify that it should be distinct (Select Distinct student_id). I
don't change the enrollment_date field. The answer that comes back is
252.
*
This happens to me a lot when I think I am doing a simple count of ID
numbers. Can someone please explain to me why there is always a
difference? What am I doing wrong? There is obviously something
about the Count function that I don't get.

Thank you for your time.
S
 
Count simply counts the rows returned. Thats it.
Do you have student ids in there multiple times?
Why not just create a query to return the rows rather than just a count,
then you can sort them, print them out and eyeball them and you should be
able to see what the differences are.

-Dorian
 
Sounds as if you need to do the distinct query first and then use it as
the source to do a count. You can do that all in one query if your
field and table names following the naming conventions (letters,
numbers, and underscore characters only). From what you posted, you
should be able to get the count using the following.

SELECT Count(StudentID) as StudentCount
FROM (
SELECT DISTINCT StudentID
FROM tblEnrollment
WHERE Enrollment_Date >=#1/1/2008#
) as DistStudID



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Sounds as if you need to do the distinct query first and then use it as
the source to do a count.  You can do that all in one query if your
field and table names following the naming conventions (letters,
numbers, and underscore characters only).  From what you posted, you
should be able to get the count using the following.

SELECT Count(StudentID) as StudentCount
FROM (
SELECT DISTINCT StudentID
FROM tblEnrollment
WHERE Enrollment_Date >=#1/1/2008#
) as DistStudID

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2008
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================




- Show quoted text -
Click to expand...

Thanks to both of you. Your advice is much appreciated.
Seems like Access makes you jump thru quite a few hoops just to get a
count, but it is what it is.
Thanks again.
 
Back
Top