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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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.
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.
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.
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.
ASKER
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.
ASKER