By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

counting or total

P: 14
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.
Nov 15 '06 #1
Share this Question
Share on Google+
19 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 16 '06 #2

P: 14
Here you go.

SELECT EXETER_SSS_STUDENT_SUMMARY_V.FULL_NAME, EXETER_SSS_STUDENT_SUMMARY_V.SSN, EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM, EXETER_SSS_SECTIONS.COURSE_NAME, EXETER_SSS_SECTIONS.NAME, EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01
FROM ((((EXETER_SSS_STUDENT_ENROLLMENTS INNER JOIN EXETER_SSS_STUDENT_GRADES ON EXETER_SSS_STUDENT_ENROLLMENTS.ENROLLMENT_ID = EXETER_SSS_STUDENT_GRADES.ENROLLMENT_ID) INNER JOIN (EXETER_SSS_STUDENT_INSTANCE INNER JOIN EXETER_SSS_STUDENT_SUMMARY_V ON EXETER_SSS_STUDENT_INSTANCE.STUDENT_INSTANCE_ID = EXETER_SSS_STUDENT_SUMMARY_V.STUDENT_INSTANCE_ID) ON EXETER_SSS_STUDENT_ENROLLMENTS.PERSON_ID = EXETER_SSS_STUDENT_INSTANCE.PERSON_ID) INNER JOIN EXETER_SSS_GRADE_WEIGHTS ON EXETER_SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID = EXETER_SSS_GRADE_WEIGHTS.GRADE_WEIGHTS_ID) INNER JOIN EXETER_SSS_SECTIONS ON EXETER_SSS_STUDENT_ENROLLMENTS.SECTION_ID = EXETER_SSS_SECTIONS.SECTION_ID) INNER JOIN EXETER_CMN_TERMS ON EXETER_SSS_STUDENT_ENROLLMENTS.TERM_ID = EXETER_CMN_TERMS.TERM_ID
WHERE (((EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM)="JD") AND ((EXETER_SSS_SECTIONS.COURSE_NAME)="4L04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4E06" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4B04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4L06") AND ((EXETER_CMN_TERMS.TERM_NAME)="2005 FA" Or (EXETER_CMN_TERMS.TERM_NAME)="2006 SP"))
ORDER BY EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01 DESC;
Nov 16 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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_STUDENT_SUMMARY_V.FULL_NAME, EXETER_SSS_STUDENT_SUMMARY_V.SSN, EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM, EXETER_SSS_SECTIONS.COURSE_NAME, EXETER_SSS_SECTIONS.NAME, EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01,
DCount("[COURSE_NAME]","ThisQueryName","[FULL_NAME]=" & [FULL_NAME]) AS CourseCount
FROM ((((EXETER_SSS_STUDENT_ENROLLMENTS INNER JOIN EXETER_SSS_STUDENT_GRADES ON EXETER_SSS_STUDENT_ENROLLMENTS.ENROLLMENT_ID = EXETER_SSS_STUDENT_GRADES.ENROLLMENT_ID) INNER JOIN (EXETER_SSS_STUDENT_INSTANCE INNER JOIN EXETER_SSS_STUDENT_SUMMARY_V ON EXETER_SSS_STUDENT_INSTANCE.STUDENT_INSTANCE_ID = EXETER_SSS_STUDENT_SUMMARY_V.STUDENT_INSTANCE_ID) ON EXETER_SSS_STUDENT_ENROLLMENTS.PERSON_ID = EXETER_SSS_STUDENT_INSTANCE.PERSON_ID) INNER JOIN EXETER_SSS_GRADE_WEIGHTS ON EXETER_SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID = EXETER_SSS_GRADE_WEIGHTS.GRADE_WEIGHTS_ID) INNER JOIN EXETER_SSS_SECTIONS ON EXETER_SSS_STUDENT_ENROLLMENTS.SECTION_ID = EXETER_SSS_SECTIONS.SECTION_ID) INNER JOIN EXETER_CMN_TERMS ON EXETER_SSS_STUDENT_ENROLLMENTS.TERM_ID = EXETER_CMN_TERMS.TERM_ID
WHERE (((EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM)="JD") AND ((EXETER_SSS_SECTIONS.COURSE_NAME)="4L04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4E06" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4B04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4L06") AND ((EXETER_CMN_TERMS.TERM_NAME)="2005 FA" Or (EXETER_CMN_TERMS.TERM_NAME)="2006 SP"))
ORDER BY EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01 DESC;[/quote]
Nov 16 '06 #4

P: 14
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.' "
Nov 16 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 16 '06 #6

P: 14
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?
Nov 16 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
Nov 16 '06 #8

P: 14
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!
Nov 17 '06 #9

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 17 '06 #10

MMcCarthy
Expert Mod 10K+
P: 14,534

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?
Nov 17 '06 #11

P: 14
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_STUDENT_SUMMARY_V.FULL_NAME, EXETER_SSS_STUDENT_SUMMARY_V.SSN, EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM, EXETER_SSS_SECTIONS.COURSE_NAME, EXETER_SSS_SECTIONS.NAME, EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01, DCount("[COURSE_NAME]","qry_Elmendorf Award","[SSN]='" & [SSN] & "'") AS CourseCount
FROM ((((EXETER_SSS_STUDENT_ENROLLMENTS INNER JOIN EXETER_SSS_STUDENT_GRADES ON EXETER_SSS_STUDENT_ENROLLMENTS.ENROLLMENT_ID=EXETE R_SSS_STUDENT_GRADES.ENROLLMENT_ID) INNER JOIN (EXETER_SSS_STUDENT_INSTANCE INNER JOIN EXETER_SSS_STUDENT_SUMMARY_V ON EXETER_SSS_STUDENT_INSTANCE.STUDENT_INSTANCE_ID=EX ETER_SSS_STUDENT_SUMMARY_V.STUDENT_INSTANCE_ID) ON EXETER_SSS_STUDENT_ENROLLMENTS.PERSON_ID=EXETER_SS S_STUDENT_INSTANCE.PERSON_ID) INNER JOIN EXETER_SSS_GRADE_WEIGHTS ON EXETER_SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID=EXETER_ SSS_GRADE_WEIGHTS.GRADE_WEIGHTS_ID) INNER JOIN EXETER_SSS_SECTIONS ON EXETER_SSS_STUDENT_ENROLLMENTS.SECTION_ID=EXETER_S SS_SECTIONS.SECTION_ID) INNER JOIN EXETER_CMN_TERMS ON EXETER_SSS_STUDENT_ENROLLMENTS.TERM_ID=EXETER_CMN_ TERMS.TERM_ID
WHERE (((EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM)="JD") AND ((EXETER_SSS_SECTIONS.COURSE_NAME)="4L04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4E06" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4B04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4L06") AND ((EXETER_CMN_TERMS.TERM_NAME)="2005 FA" Or (EXETER_CMN_TERMS.TERM_NAME)="2006 SP"))
ORDER BY EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01 DESC;
Nov 17 '06 #12

NeoPa
Expert Mod 15k+
P: 31,186
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.
Nov 17 '06 #13

PEB
Expert 100+
P: 1,418
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_STUDENT_SUMMARY_V.FULL_NAME, EXETER_SSS_STUDENT_SUMMARY_V.SSN, EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM, EXETER_SSS_SECTIONS.COURSE_NAME, EXETER_SSS_SECTIONS.NAME, EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01,
DCount("[COURSE_NAME]","ThisQueryName","[FULL_NAME]=" & [FULL_NAME]) AS CourseCount
FROM ((((EXETER_SSS_STUDENT_ENROLLMENTS INNER JOIN EXETER_SSS_STUDENT_GRADES ON EXETER_SSS_STUDENT_ENROLLMENTS.ENROLLMENT_ID = EXETER_SSS_STUDENT_GRADES.ENROLLMENT_ID) INNER JOIN (EXETER_SSS_STUDENT_INSTANCE INNER JOIN EXETER_SSS_STUDENT_SUMMARY_V ON EXETER_SSS_STUDENT_INSTANCE.STUDENT_INSTANCE_ID = EXETER_SSS_STUDENT_SUMMARY_V.STUDENT_INSTANCE_ID) ON EXETER_SSS_STUDENT_ENROLLMENTS.PERSON_ID = EXETER_SSS_STUDENT_INSTANCE.PERSON_ID) INNER JOIN EXETER_SSS_GRADE_WEIGHTS ON EXETER_SSS_STUDENT_GRADES.GRADE_WEIGHTS_ID = EXETER_SSS_GRADE_WEIGHTS.GRADE_WEIGHTS_ID) INNER JOIN EXETER_SSS_SECTIONS ON EXETER_SSS_STUDENT_ENROLLMENTS.SECTION_ID = EXETER_SSS_SECTIONS.SECTION_ID) INNER JOIN EXETER_CMN_TERMS ON EXETER_SSS_STUDENT_ENROLLMENTS.TERM_ID = EXETER_CMN_TERMS.TERM_ID
WHERE (((EXETER_SSS_STUDENT_SUMMARY_V.PROGRAM)="JD") AND ((EXETER_SSS_SECTIONS.COURSE_NAME)="4L04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4E06" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4B04" Or (EXETER_SSS_SECTIONS.COURSE_NAME)="4L06") AND ((EXETER_CMN_TERMS.TERM_NAME)="2005 FA" Or (EXETER_CMN_TERMS.TERM_NAME)="2006 SP"))
ORDER BY EXETER_SSS_STUDENT_GRADES.ATTRIBUTE01 DESC;
[/quote]
Nov 18 '06 #14

P: 14
PEB,
So how would I do this task?
Richard
Nov 22 '06 #15

NeoPa
Expert Mod 15k+
P: 31,186
I know very little about CrossTab queries, but I do know there is a wizard for it.
If you can find nothing in there, post back here for PEB (or even someone else) to respond to.
Nov 22 '06 #16

P: 14
i found the Wizard for the Crosstab. thanks again!
but no luck when creating the report i need. By using the wizard I was able to create the header row to show; students, ssn, total of courses taken, but i couldnt get it to list the courses taken.
For Example
The wizard created this.
Name, SSN, # of Courses, Math, English, History
John, SSN#, 3, 1, 1, 1
Sally, SSN#, 2, 1, , 1

I would like this.
Name, SSN, # of Courses, Math, English, History
John, SSN#, 3, Math, English, History
Sally, SSN#, 2, Math, History


I hope this makes sense.
Nov 22 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
i found the Wizard for the Crosstab. thanks again!
but no luck when creating the report i need. By using the wizard I was able to create the header row to show; students, ssn, total of courses taken, but i couldnt get it to list the courses taken.
For Example
The wizard created this.
Name, SSN, # of Courses, Math, English, History
John, SSN#, 3, 1, 1, 1
Sally, SSN#, 2, 1, , 1

I would like this.
Name, SSN, # of Courses, Math, English, History
John, SSN#, 3, Math, English, History
Sally, SSN#, 2, Math, History


I hope this makes sense.
Crosstabs are designed to do the first operation which is essentially a calculation not the second.

However, you could take the results of the first query and do a second query something like:

SELECT Name, SSN, [# of Courses],
IIf([Math]=1, "Math",""),
IIf([English]=1, "English",""),
IIf([History]=1, "History","")
FROM crosstabqueryname;

Mary
Nov 22 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
That's a good solution (what else would you expect from Mary?).
From what I've learned of CrossTab queries recently though (only from here - no personal experience), the data is returned exclusively in string form.
In which case what would be required is :
Expand|Select|Wrap|Line Numbers
  1. SELECT Name, SSN, [# of Courses], 
  2. IIf([Math]='1', 'Math',''), 
  3. IIf([English]='1', 'English',''),
  4. IIf([History]='1', 'History','')
  5. FROM crosstabqueryname
(In CODE tags of course.)
Nov 22 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
That's a good solution (what else would you expect from Mary?).
From what I've learned of CrossTab queries recently though (only from here - no personal experience), the data is returned exclusively in string form.
In which case what would be required is :
Expand|Select|Wrap|Line Numbers
  1. SELECT Name, SSN, [# of Courses], 
  2. IIf([Math]='1', 'Math',''), 
  3. IIf([English]='1', 'English',''),
  4. IIf([History]='1', 'History','')
  5. FROM crosstabqueryname
You may be right, haven't checked in a while.

(In CODE tags of course.)
OK, OKAYYYYYYYYYYY, I get the message. I forgot, so leave me alone.

Nov 22 '06 #20

Post your reply

Sign in to post your reply or Sign up for a free account.