How do I inner join a union query with another query in Access 2007?

This is a question developed from this previous post: http://www.experts-exchange.com/Database/MS_Access/Q_28651783.html

I took the query to extract a minimum date. The problem was, I have to enter training in my database sometimes without a class, so the first query excluded those individual entries in a "training table."

I took the UserSkill query and union joined it with the training table to make sure I got all the training entries, like this:

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_Skills.Skill_ID_pk = qryUserSkill_MinDate.Skill_ID_pk) AND (tbl_AM_Operators.User_ID = qryUserSkill_MinDate.User_ID_fk) AND (tbl_Classes.Training_Date = qryUserSkill_MinDate.MinTrngDate)
ORDER BY tbl_AM_Operators.Last_Name, tbl_Classes.Shift, tbl_Classes.Class_Name

UNION SELECT

tbl_Training.Training_ID_pk AS 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_Training.Skill_ID_fk, tbl_Training.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_AM_Operators.Shift, "Individual Training" AS Class_Name, tbl_Training.User_ID_fk
FROM (tbl_Skills INNER JOIN tbl_Training ON tbl_Skills.Skill_ID_pk=tbl_Training.Skill_ID_fk) INNER JOIN tbl_AM_Operators ON tbl_Training.User_ID_fk=tbl_AM_Operators.User_ID;

The problem is, the subquery imbedded in the first half of the Union query only applies to that half of the union. I'm trying to figure out how to construct the union query so that the subquery that gets the minimum date would apply to the whole union. This is the subquery.

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;

I tried tacking it again into the second half of the query, which might still work if it is just that I don't know the proper syntax.

I was also thinking of making the union query without the subquery and then inner joining the subquery as an actual subquery object, but I don't know how to do that.

I've uploaded the database with the union query that I am using as indicated in the top part of this post: quniClassandIndividual.
SampleSkills.accdb
LVL 1
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
Suggestion quick and simple: why dont you create a VIEW in access, with your union query, and use it in the query that you need to calculate the MIN(date)?

https://msdn.microsoft.com/en-us/library/bb177895(v=office.12).aspx
0
 
Anthony PerkinsCommented:
In TSQL the ORDER BY has to be at the end.  You cannot have SELECT ... FROM ... ORDER BY UNION SELECT ... (unless you are using TOP).

You also cannot use table aliases in the ORDER BY clause in a UNION'd statement.

Also for performance reasons you may want to consider using UNION ALL instead of UNION.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
David, do you still need help with this question?
0
 
David BigelowStaff Operations SpecialistAuthor Commented:
I ended up not worrying about trying to filter potential duplicate training from the secondary table. If it happens, the users can just delete it out from the employee's record. The secondary table has an easier option for deleting.
0
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.

All Courses

From novice to tech pro — start learning today.