At work, our team has recently started to offer eLearning courses. These courses are “bite-sized” (typically 15-20 minutes) modules, covering topics such as File Management, Excel formulae and setting mailbox permissions in Outlook.
Whereas delegates have to book if they want to attend a classroom-based course or a virtual training session, the eLearning courses (which have been created using Articulate) are hosted on our team’s website and can be accessed at any time by simply clicking on the name of the course on the website’s Course Catalogue page.
Unfortunately we don’t have access to a Learning Management System so in order to track usage of the eLearning, I added an additional table (tblTracking) into the Customer Relationship Management system that I built. The CRM system stores it’s data in a SQL Server database and integrates with the website to allow delegates to see what courses are available, when they are running and ultimately book a place.
When a user clicks on the name of a course on the website’s Course Catalogue page the following happens…if the selected course is an eLearning course (indicated by a value of True in the ysnELearning field in the tblCatalogue table) a record is added to the tblTracking table. It records the user’s Name (this is actually their Windows logonID and is automatically captured), the name of the course and the date.
As all my colleagues are familiar with Access, I’ve created an Access database that is linked to the SQL Server database. The Access database lives on a file share that we all have access to and provides an easy way to query the database.
In the screenshot below (which uses fictitious data), you can see that User1 has accessed the Excel Functions course and the File Management course. In fact this user accessed the Excel Functions course twice – once on 15th June and once on 18th June. This in itself is useful information as not only can we track usage of the eLearning, but we can also see whether people are accessing individual modules more than once. Users could do this for a variety of reasons – maybe they just want to run through it again or maybe they were interrupted part-way through and decided to start again from the beginning.
Another request from my Manager was to produce a list of users who had accessed each module but exclude users who had accessed the same module more that once.
To do this I created a new query:
I added the Name, Course and Date fields and then clicked the Totals button to display the Total row in the Query Grid. The Total row entry for strName and strCourse was left as Group By but the dtmDate was changed to First. This instructs Access to display the first date it finds for a combined Name/Course and so gives the desired result: