We help IT Professionals succeed at work.
Get Started

How can I not have duplicate records in a query that requires non-distinct columns

David Bigelow
on
399 Views
Last Modified: 2016-02-10
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:
SELECT tbl_Classes.Class_ID_pk
, tbl_AM_Operators.User_ID
, tbl_AM_Operators.Last_Name
, tbl_AM_Operators.First_Name
, tbl_Skills.Flow_fk
, tbl_Skills.Queue_fk
, tbl_Class_Skills.Skill_ID_fk
, tbl_Classes.Training_Date
, tbl_AM_Operators.Supervisor_ID
, tbl_AM_Operators.Bank
, tbl_AM_Operators.Status
, tbl_AM_Operators.Status_Date
, tbl_AM_Operators.Return_Date
, tbl_Classes.Shift
, tbl_Classes.Class_Name
, tbl_Class_Rosters.User_ID_fk

FROM tbl_Skills
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_fk
) ON tbl_Classes.Class_ID_pk = tbl_Class_Rosters.Class_ID_fk
) INNER JOIN tbl_Class_Skills ON tbl_Classes.Class_ID_pk = tbl_Class_Skills.Class_ID_fk
) ON tbl_Skills.Skill_ID_pk = tbl_Class_Skills.Skill_ID_fk

ORDER BY tbl_AM_Operators.Last_Name
, tbl_Classes.Shift
, tbl_Classes.Class_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
Comment
Watch Question
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This problem has been solved!
Unlock 2 Answers and 24 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE