How do I concatenate the first and last name on a data connection spreadsheet?

I am populating an Excel 2007 spreadsheet with data from a union query in Access 2007. The first and last names, being different value fields, end up on different rows in the spreadsheet. How would I concatenate them into a single cell? I want them in the order of Last name, First name. Sample spreadsheet attached: EmployeeSkills.xlsx

The original query also includes a reference to the User_ID_fk, which is a concatenation of the first and last names, but unfortunately, the output is just the User_ID, the primary key. The original query is quniClassAndIndividual, if that can be modified to output the name to the spreadsheet as already concatenated. In other words, I'm open to a solution that works, regardless of the process, to concatenate the first and last names in the spreadsheet. Sample database attached: SampleSkills.accdb.
EmployeeSkills.xlsx
SampleSkills.accdb
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.

PatHartmanCommented:
I am not able to download your database at this time so I'm going to give you some general suggestions.
1.  Looking at the spreadsheet it seems that you shouldn't be using a Union query at all.
2.  To show the name in the crosstab query, just join to the table that includes the name.

Select q.*, t.Last, t.First
From qYourCrosstab as q Inner Join youremployeetable as t on q.EmpID = t.EmpID;
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
Danny ChildIT ManagerCommented:
In cell S4, I've put this formula
=IF(ISTEXT(B4),A3&B4,"")
I did this so far over as I didn't want to mess with your Pivot Table.
 - this checks for text existing in the FirstName column, and if it exists, it joins LastName and FirstName together (with no space between them).  If the FirstName is blank, it just returns the null "" value.
If you copy this down the whole column, it will obviously only produce your LastFirst result in every alternate cell.

If you want a space in between, just use this formula
=IF(ISTEXT(B4),A3&" "&B4,"")
0
David BigelowStaff Operations SpecialistAuthor Commented:
Hope to be working on this today.
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:
Pat,
I wanted to see if you were able to download the database, yet. The reason I'm using a union query is that I'm joining records from two tables of training into one dynaset (I think it's called that) table. One table is pretty much empty, but I needed it should supervisors need to add training outside of a class environment.

Could you give instructions for the database as it is? Often I'm asked to change the design of my database, which I appreciate because I know it is from experience and a better design, but it is without instructions on how to deal with the fallout of what's affected in other areas, so I'm unable to use the instructions.
0
David BigelowStaff Operations SpecialistAuthor Commented:
I couldn't understand Pat's solution, though I appreciate how he's used abbreviated communication. Maybe before too long I'll be able to use and understand it myself.

DanCh99's solution wasn't quite what I was looking for. Plus, I didn't want to keep going into the spreadsheet to add formulas to additional rows when employees were added.

In the end, I just used the pivot table function to keep the names in the same column, albeit different rows.
0
PatHartmanCommented:
My suggestion showed how to use a query to join to the lookup table which as it happens, would have solved your problem.  I did download your database and it seems that the problem is caused because you are using table level lookups.  This "feature" is intended for novice users who will never use forms or queries or code.  Once you have expanded your skill set beyond simply opening a table in datasheet view and typing as if you were using Excel, you have exceeded the limits of usefulness of the lookup fields and have crossed into the "they're gonna' bite you territory".  My best suggestion is remove them all.  As you can see, they do not work with union queries.  The other places where they will cause you problems are too numerous to mention.  Just search any Access forum and the advice from the experts is 100% consistent on this point - don't use table level lookups, period!

The other problem I noticed is that in some tables you have autonumbers but the autonumbers are not designated as the primary key.  That is an accident waiting to happen due to a long standing bug in Jet/ACE.  Either remove them if you are using natural keys or make them the PK.  You can add an additional unique index to enforce the uniqueness business rule for the field you currently have marked as primary.
0
David BigelowStaff Operations SpecialistAuthor Commented:
Thank you, Pat, for giving this eaglet a nudge out of the nest. I've turned the database over to the users and my managers have asked me to work on other projects, to address db issues and improvements as I have time. I've put your comments at the top of that list.
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.