If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Counting Confusion
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 |
#2
|
|||
|
|||
Counting Confusion
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 "Dazed And Confused" wrote: 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 |
#3
|
|||
|
|||
Counting Confusion
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 '================================================= === Dorian wrote: 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 "Dazed And Confused" wrote: 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 |
#4
|
|||
|
|||
Counting Confusion
On Dec 5, 6:23*am, John Spencer wrote:
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 '================================================= === Dorian wrote: 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 "Dazed And Confused" wrote: 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- Hide quoted text - - Show quoted text - 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. |
Thread Tools | |
Display Modes | |
|
|