Results 1 to 6 of 6
  1. #1
    sibyseb is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3

    Problem with getting appropriately grouped report. ? Ignorance or flaw in design

    I am creating a simple database to record progress of students with some procedures. I have a list of students (less than 10) and each of them is supposed to do procedures. There is a lookup list of 8 procedures. Each one is supposed to do each procedure more than once. I want to produce a report detailing the number of times each of the 8 procedures were done by each student. I am unable to get such a grouping. I have attached an example. I created a lookup list table of procedures and students and another table storing the main data. When each student does any of the procedure, it is recorded as one record.
    I would like to know if the table design need to be restructured or if I am getting the reporting technique wrong.
    (I am learning ms access and may be missing some obvious but simple steps)


    Thank you
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.......

    Maybe this???

    I changed a few names.
    Shouldn't use spaces in object names.
    "ID" in every table is a poor naming convention.
    "Procedure" is a reserved word in Access.

    I created a totals query and used that query as the report record source and added sorting on procedure name.
    Attached Files Attached Files

  3. #3
    sibyseb is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3
    Thank you Steve for your help and advice. You got it spot on. The only little thing I would have liked was to show a count of 0 for procedures not done rather than not mentioning it.
    This was in fact a sub-report section of a database I have been creating. The file I sent was for demo, but you illustrated nicely how to implement my idea. Let me get on with the rest.
    Siby.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Happy to help.....

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Make this query:

    Code:
    SELECT TblStudents.StudentID_PK, TblProcedureList.ProcedureID_PK
    FROM TblStudents, TblProcedureList;
    Name it qryStudentProcedureCartesian

    Create this query with the result:

    Code:
    SELECT qryStudentProcedureCartesian.StudentID_PK, TblStudents.StudentName, qryStudentProcedureCartesian.ProcedureID_PK, TblProcedureList.txtProcedure, Count(TblProceduresStudent.StudentProcID) AS CountOfStudentProcID
    FROM ((qryStudentProcedureCartesian LEFT JOIN TblProceduresStudent ON (qryStudentProcedureCartesian.StudentID_PK = TblProceduresStudent.StudentID_FK) AND (qryStudentProcedureCartesian.ProcedureID_PK = TblProceduresStudent.ProcedureID_FK)) LEFT JOIN TblProcedureList ON qryStudentProcedureCartesian.ProcedureID_PK = TblProcedureList.ProcedureID_PK) LEFT JOIN TblStudents ON qryStudentProcedureCartesian.StudentID_PK = TblStudents.StudentID_PK
    GROUP BY qryStudentProcedureCartesian.StudentID_PK, TblStudents.StudentName, qryStudentProcedureCartesian.ProcedureID_PK, TblProcedureList.txtProcedure;
    this will give you a count of all the zero records as well.

    or if you are comfortable with nested SQL statements

    Code:
    SELECT CPS.spk, TblStudents.StudentName, CPS.ppk, TblProcedureList.txtProcedure, Count(PS.StudentProcID) AS ProcCount
    FROM (((SELECT TblStudents.StudentID_PK AS SPK, TblProcedureList.ProcedureID_PK AS PPK
    FROM TblStudents, TblProcedureList)  AS CPS LEFT JOIN TblProceduresStudent AS PS ON (CPS.PPK = PS.StudentID_FK) AND (CPS.SPK = PS.ProcedureID_FK)) LEFT JOIN TblStudents ON CPS.SPK = TblStudents.StudentID_PK) LEFT JOIN TblProcedureList ON CPS.PPK = TblProcedureList.ProcedureID_PK
    GROUP BY CPS.spk, TblStudents.StudentName, CPS.ppk, TblProcedureList.txtProcedure;

  6. #6
    sibyseb is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3
    Thank you rpeare for your excellent solution. Although I don't know sql queries much, when copied into access query, both of your solutions worked brilliantly.
    I gather there are many helpful experts supporting this forum
    Siby

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Report Grouped By First Leter Of a Name
    By WickidWe in forum Reports
    Replies: 4
    Last Post: 01-01-2014, 03:32 PM
  2. Problem in the design report
    By azhar2006 in forum Reports
    Replies: 28
    Last Post: 12-15-2013, 01:11 PM
  3. Excel(lent) Ignorance...
    By rpeare in forum Programming
    Replies: 18
    Last Post: 11-28-2012, 01:20 PM
  4. Grouped Report with Charts
    By FL_Boy in forum Reports
    Replies: 2
    Last Post: 12-21-2011, 09:27 PM
  5. Replies: 2
    Last Post: 08-25-2010, 01:42 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums