Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007

    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]

    FROM [Q_ReadyForUQ]

    UNION ALL SELECT [StudentName] & " " & [StudentSurname], [SubjectSortNo], [StudentClass], [SubjectName], [SchoolGrade], [AppPartGrade], [ExamGrade], [SACEGrade]

    FROM [Q_ReadyForUQ]

    WHERE [DuplicateReport] = Yes;

    Can anybody help here? Thanks.

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    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

  3. #3
    Join Date
    Jan 2007
    Provided Answers: 10
    I'd suggest a different method...
    SELECT studentName & " " & studentSurname As [sudent]
         , subjectName
         , schoolGrade
    FROM   Q_ReadyForUQ
        BY studentName & " " & studentSurname
         , subjectName
         , schoolGrade
    HAVING Count(*) = 7
    OR     Count(*) = 14
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts