counting or total

[richard5150]

I've created a query which pulls the classes for which a student has taken. Is there a way to add a count to the report to show the total of classes a student has taken?

The report is currently showing:
(Richard class1, class2, class3)

I would like the report to show this:
(Richard class1, class2, class3 - Total classes 3)

As always, your help is always appreciated.

[MMcCarthy]

I've created a query which pulls the classes for which a student has taken. Is there a way to add a count to the report to show the total of classes a student has taken?

The report is currently showing:
(Richard class1, class2, class3)

I would like the report to show this:
(Richard class1, class2, class3 - Total classes 3)

As always, your help is always appreciated.

You will need to post the SQL for your query. This looks like a crosstab query but I can't really tell. Once you post the full sql we can give you a better idea how to get the count.

[richard5150]

Here you go.

SELECT EXETER_SSS_STUD ENT_SUMMARY_V.F ULL_NAME, EXETER_SSS_STUD ENT_SUMMARY_V.S SN, EXETER_SSS_STUD ENT_SUMMARY_V.P ROGRAM, EXETER_SSS_SECT IONS.COURSE_NAM E, EXETER_SSS_SECT IONS.NAME, EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01
FROM ((((EXETER_SSS_ STUDENT_ENROLLM ENTS INNER JOIN EXETER_SSS_STUD ENT_GRADES ON EXETER_SSS_STUD ENT_ENROLLMENTS .ENROLLMENT_ID = EXETER_SSS_STUD ENT_GRADES.ENRO LLMENT_ID) INNER JOIN (EXETER_SSS_STU DENT_INSTANCE INNER JOIN EXETER_SSS_STUD ENT_SUMMARY_V ON EXETER_SSS_STUD ENT_INSTANCE.ST UDENT_INSTANCE_ ID = EXETER_SSS_STUD ENT_SUMMARY_V.S TUDENT_INSTANCE _ID) ON EXETER_SSS_STUD ENT_ENROLLMENTS .PERSON_ID = EXETER_SSS_STUD ENT_INSTANCE.PE RSON_ID) INNER JOIN EXETER_SSS_GRAD E_WEIGHTS ON EXETER_SSS_STUD ENT_GRADES.GRAD E_WEIGHTS_ID = EXETER_SSS_GRAD E_WEIGHTS.GRADE _WEIGHTS_ID) INNER JOIN EXETER_SSS_SECT IONS ON EXETER_SSS_STUD ENT_ENROLLMENTS .SECTION_ID = EXETER_SSS_SECT IONS.SECTION_ID ) INNER JOIN EXETER_CMN_TERM S ON EXETER_SSS_STUD ENT_ENROLLMENTS .TERM_ID = EXETER_CMN_TERM S.TERM_ID
WHERE (((EXETER_SSS_S TUDENT_SUMMARY_ V.PROGRAM)="JD" ) AND ((EXETER_SSS_SE CTIONS.COURSE_N AME)="4L04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4E06" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4B04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4L06") AND ((EXETER_CMN_TE RMS.TERM_NAME)= "2005 FA" Or (EXETER_CMN_TER MS.TERM_NAME)=" 2006 SP"))
ORDER BY EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01 DESC;

[MMcCarthy]

I don't know if Access will let you do this but its the only option that I can see. BTW, replace 'ThisQueryName' with the name of this query.

SELECT EXETER_SSS_STUD ENT_SUMMARY_V.F ULL_NAME, EXETER_SSS_STUD ENT_SUMMARY_V.S SN, EXETER_SSS_STUD ENT_SUMMARY_V.P ROGRAM, EXETER_SSS_SECT IONS.COURSE_NAM E, EXETER_SSS_SECT IONS.NAME, EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01,
DCount("[COURSE_NAME]","ThisQueryName","[FULL_NAME]=" & [FULL_NAME]) AS CourseCount
FROM ((((EXETER_SSS_ STUDENT_ENROLLM ENTS INNER JOIN EXETER_SSS_STUD ENT_GRADES ON EXETER_SSS_STUD ENT_ENROLLMENTS .ENROLLMENT_ID = EXETER_SSS_STUD ENT_GRADES.ENRO LLMENT_ID) INNER JOIN (EXETER_SSS_STU DENT_INSTANCE INNER JOIN EXETER_SSS_STUD ENT_SUMMARY_V ON EXETER_SSS_STUD ENT_INSTANCE.ST UDENT_INSTANCE_ ID = EXETER_SSS_STUD ENT_SUMMARY_V.S TUDENT_INSTANCE _ID) ON EXETER_SSS_STUD ENT_ENROLLMENTS .PERSON_ID = EXETER_SSS_STUD ENT_INSTANCE.PE RSON_ID) INNER JOIN EXETER_SSS_GRAD E_WEIGHTS ON EXETER_SSS_STUD ENT_GRADES.GRAD E_WEIGHTS_ID = EXETER_SSS_GRAD E_WEIGHTS.GRADE _WEIGHTS_ID) INNER JOIN EXETER_SSS_SECT IONS ON EXETER_SSS_STUD ENT_ENROLLMENTS .SECTION_ID = EXETER_SSS_SECT IONS.SECTION_ID ) INNER JOIN EXETER_CMN_TERM S ON EXETER_SSS_STUD ENT_ENROLLMENTS .TERM_ID = EXETER_CMN_TERM S.TERM_ID
WHERE (((EXETER_SSS_S TUDENT_SUMMARY_ V.PROGRAM)="JD" ) AND ((EXETER_SSS_SE CTIONS.COURSE_N AME)="4L04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4E06" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4B04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4L06") AND ((EXETER_CMN_TE RMS.TERM_NAME)= "2005 FA" Or (EXETER_CMN_TER MS.TERM_NAME)=" 2006 SP"))
ORDER BY EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01 DESC;[/QUOTE]

[richard5150]

Ok, I went ahead and did the changes.

By doing this I rec'd an error message saying (disregard the quotations)
" Syntax error (comma) in query expression '[FULL_NAME]=Abasi, Evelyn J.' "

[MMcCarthy]

Ok, I went ahead and did the changes.

By doing this I rec'd an error message saying (disregard the quotations)
" Syntax error (comma) in query expression '[FULL_NAME]=Abasi, Evelyn J.' "

Sorry my fault

DCount("[COURSE_NAME]","ThisQueryName","[FULL_NAME]='" & [FULL_NAME] & "'") AS CourseCount

[richard5150]

Sweet!
Looks like its working, but it created another problem.
Everytime I run the query now, it lags and sometimes freezes up.
Any explanation to why its doing this?

[MMcCarthy]

Sweet!
Looks like its working, but it created another problem.
Everytime I run the query now, it lags and sometimes freezes up.
Any explanation to why its doing this?

I was afraid of something like that. The query is actually referencing itself. I'm going to see if I can get someone with a little more experience than me on this kind of data manipulation to have a look at it.

Mary

[richard5150]

Acutally I was playing around with that script. Instead of using [FULL_NAME] which is a text field, I changed it to be [SSN] which is a numerical field.
By doing this the query does not lag anymore or freezes up.
Everything works great now!
KUDOS!!! for you!.
Thanks for your help Mary!

[NeoPa]

Richard,

I suspect the lag was caused by the DCount() trying to work on a field unrelated to any index.
Redoing it to work on SSN enabled the back-end to work MUCH more efficiently.
The code itself had no error as such, but you found a more more 'sympathetic' way for it to get its results.

This was something only you could do as the indices were never posted as part of the question.
Extra 'well done' if you guessed logically what might improve the performance. (Obviously you removed the single-quotes from around the [SSN] field.

Lastly, as you say, Kudos to Mary for the original concept of using DCount of itself within the query.
It is an original (I've not seen it before) and somewhat 'off-the-wall' idea. Excellent.

[MMcCarthy]

Lastly, as you say, Kudos to Mary for the original concept of using DCount of itself within the query.
It is an original (I've not seen it before) and somewhat 'off-the-wall' idea. Excellent.

Hmmm... 'off-the-wall' and Mary.

Who'd have guessed?

[richard5150]

NeoPa,

Actually I left everything as is including the single quotes. The only change done was the SSN.
Check it out, here is the end result.

SELECT EXETER_SSS_STUD ENT_SUMMARY_V.F ULL_NAME, EXETER_SSS_STUD ENT_SUMMARY_V.S SN, EXETER_SSS_STUD ENT_SUMMARY_V.P ROGRAM, EXETER_SSS_SECT IONS.COURSE_NAM E, EXETER_SSS_SECT IONS.NAME, EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01, DCount("[COURSE_NAME]","qry_Elmendor f Award","[SSN]='" & [SSN] & "'") AS CourseCount
FROM ((((EXETER_SSS_ STUDENT_ENROLLM ENTS INNER JOIN EXETER_SSS_STUD ENT_GRADES ON EXETER_SSS_STUD ENT_ENROLLMENTS .ENROLLMENT_ID= EXETER_SSS_STUD ENT_GRADES.ENRO LLMENT_ID) INNER JOIN (EXETER_SSS_STU DENT_INSTANCE INNER JOIN EXETER_SSS_STUD ENT_SUMMARY_V ON EXETER_SSS_STUD ENT_INSTANCE.ST UDENT_INSTANCE_ ID=EXETER_SSS_S TUDENT_SUMMARY_ V.STUDENT_INSTA NCE_ID) ON EXETER_SSS_STUD ENT_ENROLLMENTS .PERSON_ID=EXET ER_SSS_STUDENT_ INSTANCE.PERSON _ID) INNER JOIN EXETER_SSS_GRAD E_WEIGHTS ON EXETER_SSS_STUD ENT_GRADES.GRAD E_WEIGHTS_ID=EX ETER_SSS_GRADE_ WEIGHTS.GRADE_W EIGHTS_ID) INNER JOIN EXETER_SSS_SECT IONS ON EXETER_SSS_STUD ENT_ENROLLMENTS .SECTION_ID=EXE TER_SSS_SECTION S.SECTION_ID) INNER JOIN EXETER_CMN_TERM S ON EXETER_SSS_STUD ENT_ENROLLMENTS .TERM_ID=EXETER _CMN_TERMS.TERM _ID
WHERE (((EXETER_SSS_S TUDENT_SUMMARY_ V.PROGRAM)="JD" ) AND ((EXETER_SSS_SE CTIONS.COURSE_N AME)="4L04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4E06" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4B04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4L06") AND ((EXETER_CMN_TE RMS.TERM_NAME)= "2005 FA" Or (EXETER_CMN_TER MS.TERM_NAME)=" 2006 SP"))
ORDER BY EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01 DESC;

[NeoPa]

Changing the DCount() to use SSN is exactly what I was talking about.
If you imagine that the Domain Aggregate function runs once FOR EVERY RECORD in the recordset, the effect of tidying that up to match an index SHOULD be huge.

[PEB]

Why do not do a CROSSTAB Query that will show you all Courses as row and you can also calculate the count of your Courses?

All this in one row...

I don't know if Access will let you do this but its the only option that I can see. BTW, replace 'ThisQueryName' with the name of this query.

SELECT EXETER_SSS_STUD ENT_SUMMARY_V.F ULL_NAME, EXETER_SSS_STUD ENT_SUMMARY_V.S SN, EXETER_SSS_STUD ENT_SUMMARY_V.P ROGRAM, EXETER_SSS_SECT IONS.COURSE_NAM E, EXETER_SSS_SECT IONS.NAME, EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01,
DCount("[COURSE_NAME]","ThisQueryName","[FULL_NAME]=" & [FULL_NAME]) AS CourseCount
FROM ((((EXETER_SSS_ STUDENT_ENROLLM ENTS INNER JOIN EXETER_SSS_STUD ENT_GRADES ON EXETER_SSS_STUD ENT_ENROLLMENTS .ENROLLMENT_ID = EXETER_SSS_STUD ENT_GRADES.ENRO LLMENT_ID) INNER JOIN (EXETER_SSS_STU DENT_INSTANCE INNER JOIN EXETER_SSS_STUD ENT_SUMMARY_V ON EXETER_SSS_STUD ENT_INSTANCE.ST UDENT_INSTANCE_ ID = EXETER_SSS_STUD ENT_SUMMARY_V.S TUDENT_INSTANCE _ID) ON EXETER_SSS_STUD ENT_ENROLLMENTS .PERSON_ID = EXETER_SSS_STUD ENT_INSTANCE.PE RSON_ID) INNER JOIN EXETER_SSS_GRAD E_WEIGHTS ON EXETER_SSS_STUD ENT_GRADES.GRAD E_WEIGHTS_ID = EXETER_SSS_GRAD E_WEIGHTS.GRADE _WEIGHTS_ID) INNER JOIN EXETER_SSS_SECT IONS ON EXETER_SSS_STUD ENT_ENROLLMENTS .SECTION_ID = EXETER_SSS_SECT IONS.SECTION_ID ) INNER JOIN EXETER_CMN_TERM S ON EXETER_SSS_STUD ENT_ENROLLMENTS .TERM_ID = EXETER_CMN_TERM S.TERM_ID
WHERE (((EXETER_SSS_S TUDENT_SUMMARY_ V.PROGRAM)="JD" ) AND ((EXETER_SSS_SE CTIONS.COURSE_N AME)="4L04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4E06" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4B04" Or (EXETER_SSS_SEC TIONS.COURSE_NA ME)="4L06") AND ((EXETER_CMN_TE RMS.TERM_NAME)= "2005 FA" Or (EXETER_CMN_TER MS.TERM_NAME)=" 2006 SP"))
ORDER BY EXETER_SSS_STUD ENT_GRADES.ATTR IBUTE01 DESC;

[/QUOTE]

[richard5150]

PEB,
So how would I do this task?
Richard