Link to home
Start Free TrialLog in
Avatar of David Bigelow
David BigelowFlag for United States of America

asked on

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;
Avatar of Nick67
Nick67
Flag of Canada image

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.
Avatar of David Bigelow

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Nick67
Nick67
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.