var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Counting number of records for query result?
I have a Student Grades database with table structure T_Students - T_StudentGrades - T_Subjects (each is linked via primary keys of same table name)
I have used a Union All query (sourced from a standard query) which duplicates the records of students whose field DuplicateReport (Y/N) has a value of True.
So, when I run my query, I get a list something like this:
Fred Bloggs / English / A / 55%
Fred Bloggs / Maths / B / 50%
Fred Bloggs / Japanese / C / 45%
Mary Smith / English / C / 41%
Mary Smith / Maths / D / 22%
Mary Smith / German / F / 2%
...and so on. Each student has multiple records like this, but some have more than others, and some have them duplicated twice, purposely.
What I need is a way to filter out the query so that I can show only students who have 7 or 14 records, or do the reverse later and select students with less than these amounts.
The reason I need this is for the purpose of simplifying a mail merge into a Word document (and we can't create reports within Access for administrative reasons)
The SQL code behind the Union All query is:
SELECT [StudentName] & " " & [StudentSurname], [SubjectSortNo], [StudentClass], [SubjectName], [SchoolGrade], [AppPartGrade], [ExamGrade], [SACEGrade]
UNION ALL SELECT [StudentName] & " " & [StudentSurname], [SubjectSortNo], [StudentClass], [SubjectName], [SchoolGrade], [AppPartGrade], [ExamGrade], [SACEGrade]
WHERE [DuplicateReport] = Yes;
Can anybody help here? Thanks.
you probably need to use a sub select which counts the number of result records set a filter/wehre clause on that to your required settings
I'd suggest a different method...
SELECT studentName & " " & studentSurname As [sudent]
BY studentName & " " & studentSurname
HAVING Count(*) = 7
OR Count(*) = 14