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.
19 1567
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.
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;
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]
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.' "
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
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?
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
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 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.
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?
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;
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.
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]
PEB,
So how would I do this task?
Richard
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.
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.
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
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 : - SELECT Name, SSN, [# of Courses],
-
IIf([Math]='1', 'Math',''),
-
IIf([English]='1', 'English',''),
-
IIf([History]='1', 'History','')
-
FROM crosstabqueryname
(In CODE tags of course.)
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 : - SELECT Name, SSN, [# of Courses],
-
IIf([Math]='1', 'Math',''),
-
IIf([English]='1', 'English',''),
-
IIf([History]='1', 'History','')
-
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. Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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"
...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
|
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...
| |