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

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
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nick67Commented:
You can use queries as sources for more queries.
So you take this query and use it as a source for a second query
Save what you posted as query named qrySkillsInitial

In a new query then
Select DISTINCT User_ID, Last_Name,  First_Name, Skill_ID_fk from qrySkillsInitial

That approach should get it done
You can get gnarly and try to jam it all into one query
Select DISTINCT User_ID, Last_Name,  First_Name, Skill_ID_fk FROM
(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
) as qrySkillsInitial
 ORDER BY Last_Name;

But that, as I said, can be gnarly to wrangle together.
0
David BigelowStaff Operations SpecialistAuthor Commented:
Nick67, thank you! That gets me started. But the angst is that I need one training date to show in the report. I'd prefer to show the earliest date (some are listed in four different classes -  they were subject matter experts and shouldn't have signed in as students after the first time, but it's about 400 records that I would have to change). If I select a training date in the new query, it then pulls all the training dates for the Skill_ID_fk.

How could I have a query that pulls the unique Skill_ID_fk and still includes one training date so I can tell when the employee was trained? Earliest date would be preferred.

(I tried to make that clear in my first paragraph, but I see now that it wasn't so clear.)
0
Jeffrey CoachmanMIS LiasonCommented:
Then lets keep this simple...
Post a small sample database that contains enough data to display the issue.
Then simply post a clear, graphical example of the exact output you require...

Sometimes the Access report setting might be used to get what you are after...
For example, there is a "Hide duplicates setting,
...It is also not clear if you could just use a Grouping...
This would display something like this:
USA
    Customer3
    Customer9
    Customer47
China
    Customer14
    Customer27
    Customer61
    Customer79

...Where the "Country" is only displayed once (not duplicated for each customer...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

David BigelowStaff Operations SpecialistAuthor Commented:
Here's the sample database. I made the two queries: qrySkillsInitial and qrySkillsDistinct.
SampleSkills.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
And where is the clear, graphical example of the exact output you require?
0
David BigelowStaff Operations SpecialistAuthor Commented:
Thanks Jeff,

That question triggered a report change, so use this database.

Queries: qrySkillsInitial and qrySkillsDistinct, as above
Report: rpt_Training_Employees now has the qrySkillsInitial as the source.

If I add the training date to the qrySkillsDistinct, both days of training are added to the report, which I don't want.

I've attached the report pdf that I want the report to look like. Of course, it has duplicate training, usually on Workload B Queue B, in this  sample, which I don't want the report to look like. Instead, one instance of each skill learned.
SampleSkills.accdb
rpt-Training-Employees.pdf
0
Jeffrey CoachmanMIS LiasonCommented:
it has duplicate training, usually on Workload B Queue B, in this  sample, which I don't want the report to look like.
Still confused, this is why I need to see *exactly* what you need....

First try setting the "Hide duplicates" property of the two fields to: Yes
Then you get this:
sample...is this what you are looking for?

JeffCoachman
0
David BigelowStaff Operations SpecialistAuthor Commented:
My apologies for not being able to reproduce the report exactly as wanted. Here's a screenshot that shows the duplicate classes crossed off. We have no need for them to appear on the report at all. They had the training on January 12 and then again on January 26. I'm wanting any subsequent training after the initial training on the same skills (workload and queue) to not appear on the report.
rpt-Training-Employees.PNG
0
Jeffrey CoachmanMIS LiasonCommented:
No problem, ...Its just that I was trying to get you a solution ASAP, ...in case you were in a bind...

OK, so it is the combination of workload and task that signifies a duplicate, ...correct?
If so, then you want the date field hidden as well?
0
David BigelowStaff Operations SpecialistAuthor Commented:
Yes, more than instance of the same combination of workload and task per employee indicates a duplicate for that employee.

I suppose hiding the date field would make the solution a snap. I was hoping to keep the date field for each combination, preferably the earliest one for duplicates.
The date field is indicated on each class roster report, which is the class name, skills learned on that day only, and employees trained.
It would be nice if the supervisor could see on the report we're working on when his staff were trained on each workload they know and not have to pull up each roster report for the date. Earlier training dates are one indication of experience.
0
Jeffrey CoachmanMIS LiasonCommented:
I suppose hiding the date field would make the solution a snap. I was hoping to keep the date field for each combination,
Confused again, ...You show the date as crossed out, ...but now you say you want to see it...?
Forget what is easier or harder for me, ...just show me, graphically, the exact results you need, and I will let you know if it is possible...

I'll dig deeper tonight

Jeff
0
David BigelowStaff Operations SpecialistAuthor Commented:
Whenever there is a repeat workload/queue combo for an employee, it means he attended training for the same combo on two different days. In the actual database, combos are repeated three or four times for some employees. I want only one instance of combos to show, that is, the first time the employee was in the class. In these illustrations, some employees attended the combo of Workload B/Queue B twice.
Where I've crossed out the combos, instead of skipping a line, the list should continue with the next line item. No indication that cleanup was done in the background.
0
PortletPaulfreelancerCommented:
I have not used Access seriously for many years, but having looked at that database I could not see any records that exhibit the problem. It is helpful to have data that does represent a problem if one exists.

Try this to see if it addresses the problem, as I could not really test it
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_AM_Operators INNER JOIN ((tbl_Classes INNER JOIN tbl_Class_Rosters ON tbl_Classes.Class_ID_pk = tbl_Class_Rosters.Class_ID_fk) INNER JOIN (tbl_Skills INNER JOIN tbl_Class_Skills ON tbl_Skills.Skill_ID_pk = tbl_Class_Skills.Skill_ID_fk) ON tbl_Classes.Class_ID_pk = tbl_Class_Skills.Class_ID_fk) ON tbl_AM_Operators.User_ID = tbl_Class_Rosters.User_ID_fk) INNER JOIN ( SELECT
        tbl_Skills.Skill_ID_pk
      , tbl_Class_Rosters.User_ID_fk
      , MIN(tbl_Classes.Training_Date) AS MinTrngDate
FROM (tbl_Classes
INNER JOIN (tbl_Skills
INNER JOIN tbl_Class_Skills
        ON tbl_Skills.Skill_ID_pk = tbl_Class_Skills.Skill_ID_fk)
        ON tbl_Classes.Class_ID_pk = tbl_Class_Skills.Class_ID_fk)
INNER JOIN tbl_Class_Rosters
        ON tbl_Classes.Class_ID_pk = tbl_Class_Rosters.Class_ID_fk
GROUP BY tbl_Skills.Skill_ID_pk
       , tbl_Class_Rosters.User_ID_fk) as qryUserSkill_MinDate ON (tbl_Classes.Training_Date = qryUserSkill_MinDate.MinTrngDate) AND (tbl_AM_Operators.User_ID = qryUserSkill_MinDate.User_ID_fk) AND (tbl_Skills.Skill_ID_pk = qryUserSkill_MinDate.Skill_ID_pk)
ORDER BY tbl_AM_Operators.Last_Name, tbl_Classes.Shift, tbl_Classes.Class_Name;

Open in new window


This embeds a subquery that you could save seperately e.g. as  qryUserSkill_MinDate:
SELECT
        tbl_Skills.Skill_ID_pk
      , tbl_Class_Rosters.User_ID_fk
      , MIN(tbl_Classes.Training_Date) AS MinTrngDate
FROM (tbl_Classes
INNER JOIN (tbl_Skills
INNER JOIN tbl_Class_Skills
        ON tbl_Skills.Skill_ID_pk = tbl_Class_Skills.Skill_ID_fk)
        ON tbl_Classes.Class_ID_pk = tbl_Class_Skills.Class_ID_fk)
INNER JOIN tbl_Class_Rosters
        ON tbl_Classes.Class_ID_pk = tbl_Class_Rosters.Class_ID_fk
GROUP BY tbl_Skills.Skill_ID_pk
       , tbl_Class_Rosters.User_ID_fk;

Open in new window


This it can then be reused into your original query as another join, like this:
INNER JOIN qryUserSkill_MinDate
        ON (tbl_Classes.Training_Date = qryUserSkill_MinDate.MinTrngDate)
        AND (tbl_AM_Operators.User_ID = qryUserSkill_MinDate.User_ID_fk)
        AND (tbl_Skills.Skill_ID_pk = qryUserSkill_MinDate.Skill_ID_pk)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
like this?:
sample
0
David BigelowStaff Operations SpecialistAuthor Commented:
Paul, you produced exactly what I was looking for. Regarding splitting the queries as you did, would that make the overall search run faster, make it easier to understand the queries, or both?

Jeff, I can see I have a ways to go in being clear in my questions. What I thought of last night is that I should have typed exactly what I wanted in a Word document and uploaded that for a visual. Thank you for sticking with me through this!
0
Nick67Commented:
@David Bigelow
I am glad you got it working and that Paul got it working for you.
With your question, you only chose MS Access as the Topic.
Had you chosen Access, SQL Query, and SQL Server as topics, you would have had a broader selection of Experts look at your question -- and given that Access uses SQL Server frequently as a backend, those choices would have been appropriate.

When you need to get a query doing something complex and efficiently, having the guys who knock SQL around for a living as a primary task see your Q is a good thing :)

Nick67

PS mdb format for samples is preferred, too, as everyone who runs Access can run an mdb, but with an accdb, those who run A2003 -- which includes a lot of folks who didn't appreciate the way A2007+ destroyed their productivity -- can't help. :)
0
David BigelowStaff Operations SpecialistAuthor Commented:
Nick, I appreciate the tips! They are duly noted in the back of my notebook under posting tips for easy reference. You might call it an appendix.
0
David BigelowStaff Operations SpecialistAuthor Commented:
Paul, just now being able to parse what you wrote. Thank you! I see so many areas where I could have used queries instead of tables and forms instead of table lookups in my database. But, seeing that this is my second database, I think I'm doing okay - I'm learning!
0
Jeffrey CoachmanMIS LiasonCommented:
Nick,

Note that some issues can only be reproduced in the native version of Access.
for example, We had a sample once that we experts requested be saved in the mdb format.
(when .accdb was new, and a lot of us had not upgraded)
The mdb file was working fine.
 ...but the actual production file was an .accdb file, and was not working.
So we had to go back and forth for a while before we realized that it was the DB format that was the issue.
And the issue could only be addressed in the .accdb format.
(something with the VBA references, or form/report properties, IIRC...)

Jeff
0
Nick67Commented:
@Jeffrey Coachman,
Absolutely, there are things that cannot be done in an mdb.
If you have grief with split forms, multi-value fields, attachment fields, etc. an .mdb won't play.
If you are binding an image to field, I think that's A2010+ that has to be in play for that to work.

But IF you can get your sample to misbehave as an .mdb, that's the way to go.
IF you can't get a misbehaving sample going in an .mdb format, well, them's the breaks.

An .accdb is unfortunately not like a .docx, .xlsx, or .pptx where it can be opened downlevel.
0
PortletPaulfreelancerCommented:
In my role as a Topic Advisor I would not recommend using topics such as SQL Server for an Access question. MS Access is so very different in so many ways to SQL Server, the query language syntax and capabilities differ a great deal!

The accepted answer on this question would NOT BE the best approach for SQL Server (using row_number() would be) and it is hellishly difficult when answering questions to know which database is relevant if both Access & SQL Server are chosen topics. In particular this is so because Access can be the "front end" while MSSQL is the "back end" and in such scenarios SQL Server query syntax can be used.

So please, make it clear which database is really being used and don't just choose topics in the hope it attracts more experts. If a question becomes "neglected" then administrators or topic advisors may add more topics to questions, but please do trust us to know when that is necessary.
0
Nick67Commented:
@Paul.
I'd respectfully disagree.
Clearly Access was the primary topic chosen and was indeed correct, but SQL Query syntax is fairly similar across multiple platforms.  Access can query just about every platform conceived, and while the JET/Ace engine takes care of most of the details, the basic syntax and most especially the methodology carries over.  Set logic vs RBAR.  And what can look like a very daunting mess of SQL to a guy used to the QBE doesn't look that bad to a guy versed in SQL Server.  But being clear that the query is in Access, and not a View or stored procedure is important to getting good help.

Access guys start with one of the best pieces of software MS ever created: the query editor.  And while its a good tool, when it comes to really complicated queries it leads you down the road of IN or EXISTS clauses.  Which work but aren't efficient.  I don't think Access guys think about creating multiple table aliases and JOINing them in a query.  That's in the realm of unknown unknowns as it were.  And old hat to you.  And they work well in Access, too.

We are certainly agreed that shotgunning topics isn't a good idea.  I have come to learn though, that when it comes to getting kitschy Access queries beat into shape, catching the eye of guys who beat SQL Server around the ears is not a bad idea!

Nick67
0
PortletPaulfreelancerCommented:
@Nick67 sorry to disagree on the topic point. I simply ask that a question be clear on which dbms is actually being used. I answer SQL questions, but there are so many variants to that query language it is vital for me to know which "flavour" of SQL is needed.

Please note that I did get a notification about this question but did not participate simply because I have had to cut back the time devoted to voluntary work at the moment.

@others: Nick67 asked me to participate by message, so really the answer traces back to him
0
PortletPaulfreelancerCommented:
@Nick67, :) we just "cross posted"

My major point is "to be clear" about the dbms that is actually being used because the query syntax DOES vary a lot

just one tiny example: Nz() ISNULL() IFNULL() NVL() COALESCE()

In this question, the approach used COULD be used in SQL Server (or Oracle or MySQL/many others) but it isn't the optimal approach for either SQL Server or Oracle.  Knowing that only Access is relevant makes it absolutely clear what options are available.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.