With this sql code I select students, classes, and skills and make a report of what students learned. Sometimes a student is listed in more than one class and consequently has a skill listed more than once. The training date and class name make the records distinct, so it's not enough to mark the property to hide duplicate records or set the select query as distinct. I'm thinking a subquery with Top N or maybe a partition would work, but don't understand enough to go about doing that.
In the report, the Skill is listed as the Flow and the Queue, side by side, which together make up the skill. The Skill_ID is assigned to a concatenation of the Flow and Queue. Somehow I need the query to pull the Skill_ID only once for each employee and suppress any other records from other classes where the employee was listed as having the same training. In this way, the Flow and Queue would only be listed once for each skill, in the report.
The reason I don't have the Skill itself listed in the report is that a queue is often part of many Flows, so the supervisor might want to know all of the Flows in which the employee knows a certain queue. Each of those Flow/Queue combinations has it's own Skill ID, but it's easy in a drop down menu on a form to select the queue and show all the flow queue combinations for a given employee.
Here's the sql code I'm trying to modify:
INNER JOIN (
tbl_Classes INNER JOIN (
tbl_AM_Operators INNER JOIN tbl_Class_Rosters ON tbl_AM_Operators.User_ID = tbl_Class_Rosters.User_ID_
) ON tbl_Classes.Class_ID_pk = tbl_Class_Rosters.Class_ID
) INNER JOIN tbl_Class_Skills ON tbl_Classes.Class_ID_pk = tbl_Class_Skills.Class_ID_
) ON tbl_Skills.Skill_ID_pk = tbl_Class_Skills.Skill_ID_
ORDER BY tbl_AM_Operators.Last_Name
This is also posted here, which I realized later was a dba site: https://dba.stackexchange.com/questions/97225/how-do-i-modify-an-access-query-to-select-a-row-based-on-one-column-when-other-c