473,326 Members | 2,127 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

counting or total

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
19 1567
MMcCarthy
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
14,534 Expert Mod 8TB
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
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB

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
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
32,556 Expert Mod 16PB
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
1,418 Expert 1GB
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
PEB,
So how would I do this task?
Richard
Nov 22 '06 #15
NeoPa
32,556 Expert Mod 16PB
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
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
14,534 Expert Mod 8TB
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

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

Similar topics

3
by: Megan | last post by:
hi everybody- i'm having a counting problem i hope you guys and gals could give me some help with. i have a query that retrieves a bevy of information from several different tables. first let...
1
by: mjobrien | last post by:
Thanks for the hint Allen (see below). But I am already doing that count as total records read (5)in the report footer. That count is unduplicated for the record not for the field - student ID as...
18
by: ChadDiesel | last post by:
I appreciate the help on this group. I know I've posted a lot here the last couple of weeks, but I was thrown into a database project at my work with very little Access experience. No other...
1
by: j | last post by:
Hi, I've been trying to do line/character counts on documents that are being uploaded. As well as the "counting" I also have to remove certain sections from the file. So, firstly I was working...
1
by: Jerry | last post by:
We have a 10-question quiz for kids, each question being a yes or no answer using radio selections. I'd like to keep a current total of yes's and no's at the bottom of the quiz (if the user selects...
3
by: viewsonic | last post by:
Help, im a student that has to write a program for counting coins. Half of the program works but the other half doesn.t should have the following parameters. output is: Name Date total...
27
by: Simon Biber | last post by:
I was reading http://en.wikipedia.org/wiki/Poker_probability which has a good description of how to count the frequency of different types of poker hands using a mathematical approach. A sample...
11
by: Andy | last post by:
Hi, the file below will print all the keywords in a file and also the line # of the keyword. What I couldn't figure out is to count those keywords per line. For example - "Line #1 has 3 keywords" ...
8
by: xiaolim | last post by:
i making a simple program to count the different kinds of characters in a text file and then display them out, however i only manage to count the total numbers of characters. #include...
3
by: nitric | last post by:
hey guys, i'm really stuck on this program. It's basically a survey and I have to ask people what drinks they like. 1-4, coffee tea oj and lemonade. i'm having trouble counting the TOTAL NUMBER...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.