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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

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

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
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
David, do you still need help with this question?
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.
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.