Hello all,
I have a training database where we are trying to log the comment cards for each class we hold. The classes are rated (1 to 5) on course organization, instructor knowledge, the quality of the handouts and visual aids etc. I've attached a copy of my database file but here are my tables:
Classes
ClassID
ClassTitle
TrainingSessions
TrainingSessionID
TrainingSessionClassID (referencing the Classes.ClassID field)
TrainingSessionDate
TrainingSessionInstructorID (referencing the TrainingInstructors.InstructorID field)
SessionRatings
SessionRatingID
TrainingSessionID (referencing the TrainingSessions.TrainingSessionID)
TrainerKnowledge
CourseOrganization
VisualAids
etc...
TrainingInstructors
InstructorID
InstructorFirstName
InstructorLastName
We would like to create charts showing the monthly, quarterly, and yearly rating averages for each rating category, for each type of class we offer. Access is very limited in terms of charting capability so I am trying to export the data to Excel in the hopes of automating the creation of the charts, but am having difficulties creating the query (or queries) necessary to accomplish that. Crosstab queries only allow one value to be averaged (in my case) of course, so I tried creating separate crosstab queries to average each rating category field (trainer knowledge, course organization, etc.) grouped by the class ID and year of the session date, but can't seem to pull the rating averages from all those individual queries into one simple Select query that works. I guess I'm not sure which ClassID to use - just picking from one of the crosstab queries doesnt work.
I can just export the entire SessionRatings data into Excel, then manually(?) create the subtotals for the ratings for each type of class we offer, and have another sheet with a chart "template" based on the cells in the data source sheet. Not sure if I'm going to run into problems with the number of rows I'm referencing, perhaps I'll need to create a macro(?). That is more of an Excel question so I can keep working on that myself.
Apart from the standard monthly/quarterly/annual reports the idea is to have a form in Access where we can drill down the data based on the dates, instructors, and classes that we want to see ratings for as well, and get that data into charts. But again, I need to figure out the queries to get the averages for all those ratings.
I know that's a lot to be asking about, any suggestions for the best way to tackle this would be much appreciated! Thank you.
I have a training database where we are trying to log the comment cards for each class we hold. The classes are rated (1 to 5) on course organization, instructor knowledge, the quality of the handouts and visual aids etc. I've attached a copy of my database file but here are my tables:
Classes
ClassID
ClassTitle
TrainingSessions
TrainingSessionID
TrainingSessionClassID (referencing the Classes.ClassID field)
TrainingSessionDate
TrainingSessionInstructorID (referencing the TrainingInstructors.InstructorID field)
SessionRatings
SessionRatingID
TrainingSessionID (referencing the TrainingSessions.TrainingSessionID)
TrainerKnowledge
CourseOrganization
VisualAids
etc...
TrainingInstructors
InstructorID
InstructorFirstName
InstructorLastName
We would like to create charts showing the monthly, quarterly, and yearly rating averages for each rating category, for each type of class we offer. Access is very limited in terms of charting capability so I am trying to export the data to Excel in the hopes of automating the creation of the charts, but am having difficulties creating the query (or queries) necessary to accomplish that. Crosstab queries only allow one value to be averaged (in my case) of course, so I tried creating separate crosstab queries to average each rating category field (trainer knowledge, course organization, etc.) grouped by the class ID and year of the session date, but can't seem to pull the rating averages from all those individual queries into one simple Select query that works. I guess I'm not sure which ClassID to use - just picking from one of the crosstab queries doesnt work.
I can just export the entire SessionRatings data into Excel, then manually(?) create the subtotals for the ratings for each type of class we offer, and have another sheet with a chart "template" based on the cells in the data source sheet. Not sure if I'm going to run into problems with the number of rows I'm referencing, perhaps I'll need to create a macro(?). That is more of an Excel question so I can keep working on that myself.
Apart from the standard monthly/quarterly/annual reports the idea is to have a form in Access where we can drill down the data based on the dates, instructors, and classes that we want to see ratings for as well, and get that data into charts. But again, I need to figure out the queries to get the averages for all those ratings.
I know that's a lot to be asking about, any suggestions for the best way to tackle this would be much appreciated! Thank you.
Last edited:
