How do can I concatenate the first and last name in this union query in Access 2007

How could I concatenate the output of the first and last name in this query? This is used in a data connection for an excel spreadsheet. Right now, the output is first and last names in separate columns. I want them in one column, last name first.

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

Nick67Commented:
instead of picking those columns in the query
....tbl_AM_Operators.Last_Name, tbl_AM_Operators.First_Name,.....
You'd go with
(tbl_AM_Operators.Last_Name, & ", " & tbl_AM_Operators.First_Name) as LastCommaFirst
or something similar

Union queries require the same columns in all the statements, so you'd need the same thing in both halves.
David BigelowStaff Operations SpecialistAuthor Commented:
How would I work that into the query? I tried replacing the columns with the statement you suggested, but that didn't work. I didn't think that would be what you meant, but how should I use it? I know that later in my query are similar instances, which I understand conceptually, but exactly how to place this statement with any needed syntax is beyond me.
Nick67Commented:
SELECT tbl_Classes.Class_ID_pk, tbl_AM_Operators.User_ID, (tbl_AM_Operators.Last_Name & ", " &  tbl_AM_Operators.First_Name) as LastCommaFirst, 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) as LastCommaFirst, 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; 

Open in new window

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
David BigelowStaff Operations SpecialistAuthor Commented:
Good grief! I thought I tried exactly that when you initially suggested it. I don't know what I did different, but it's working now. Thank you.
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.