asked on

Query question.

I have a table with 8 text fields that contain names. IE; Lead Instructor - Instructor 2 - Instructor 3 - Instructor 4 - Monitor 1 - Monitor 2 - Monitor 3 - Monitor 4. I need to create a table that contains all of these fields in a single text field, "Name".

Hope I am clear.

ste5an

What's wrong in using a query - doing it on the fly, when you need it - instead?

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

Nothing between the names? Either way, CONCATenate them:

SELECT [Lead Instructor] + [Instructor 2] + ...rest_of_columns... AS all_names_combined

Edit: Just noticed it was Access, not SQL Server. Please ignore this comment.

PatHartman

If concatenating them is what you want, I would use the & rather than the + in case any are null and I would also include a separator;

Select [Lead Instructor] & ";" & [Instructor 2] & ";" & [Instructor 3} & ";" ....

And finally - Name is the name of a property and is a poor choice as the name of a column. use MushedNames or something that described what you did.

John Kincaid

ASKER

Pat, your suggestion of VBA is probably the best course. This is a one time situation and I was just wondering if a query could do the job.

I am all over the VBA code, thanks!

Dale Fye

Yes, you can create a normalizing query:

SELECT ID, "Lead Instructor" as Role, [Lead Instructor] as RoleName
FROM yourTable
WHERE [Lead Instructor] iS NOT NULL
UNION ALL
SELECT ID, "Instructor 2" as Role, [Instructor 2] as RoleName
FROM yourTable
WHERE [Instructor 2] iS NOT NULL
...
UNION ALL
SELECT ID, "Monitor 4" as Role, [Monitor 4] as RoleName
FROM yourTable
WHERE [Monitor 4] iS NOT NULL

John Kincaid

ASKER

It has been a while since I have done any code work and as before I am happy for the help all experts.