Combining multiple columns into one longer column (without combining fields)

[tmpgovrel]

Hello,



I am not sure if this is done with a query or what so forgive me if I could have picked a more specific subgroup.

I trying to combine three columns that I have into one column without combining fields.

Currently what I have:
(see image below)


What I want:
ID-----MOC
##----name1
##----name2
##----name3
##----name4
##----name5
etc

The list I have will be much longer and will be changing frequently, which is why I can't just go on excel and manually do this. Any and all help would be greatly appreciated!

--Ally

[ssanfu]

I'm confused....

Are the names like:
Joe, Jon, Jim, Jake, John

and you want:
Joe
Jon
Jim
Jake
John

-- OR --

You have :
Joe
Jon
Jim
Jake
John

and you want:
Joe, Jon, Jim, Jake, John

[tmpgovrel]

First scenario

[ssanfu]

I don't use multi-value fields because 1) it violates normalization rules and 2) they are a real pain in the neck (only 2.5 feet lower - I'm tall).

My solution would be to write a UDF to try and split the names, appending the names to a temp table. Each time the function executes, first delete any records in the temp table, then split the names and append them to the temp table.

[tmpgovrel]

Thanks for the advice

I think I am a little bit confused and I think I might not have been as clear as I could have when I was talking about what I currently have. At the moment, I have three columns with one name in each row for each column, I want to just combine the columns (essentially each list) and create one master list. Sorry for me just failing at explaining myself. This is my second day using Access and it hasn't been the smoothest of trips.

--Ally

[ssanfu]

No problems. Welcome to Access!

If you have a table (tblAttendees)

N_ID    moc1    abc1    xyz1
1       Joe      Tim    Stan
2       Jon      Tom    Steve
3       Jim      Ted    Sam
4       Jake     Terry  Sid
5       John     Tod    Sal

and you want to see

N_ID   moc
1      Joe, Tim, Stan
2      Jon, Tom, Steve
3      Jim, Ted, Sam
4      Jake, Terry, Sid
5      John, Tod, Sal

You would concatenate the fields.
The query would look something like

Select  N_ID, moc1 & ", " & abc1 & ", " & xyz1 AS moc FROM tblAttendees

[tmpgovrel]

I am seriously so sorry - but the output I want is just a list, with one name per field

i.e.
Joe
Jon
Jim
Jake
John

SO sorry

--Ally

[ssanfu]

S'ok

I'll try again...

You have

N_ID    MOC1   ABC1   XYZ1
1       Joe    Tim    Stan
2       Jon    Tom    Steve
3       Jim    Ted    Sam
4       Jake   Terry  Sid
5       John   Tod    Sal

And you want

N_ID     DEF
1        Joe
2        Jon
3        Jim
4        Jake
5        John
6        Tim
7        Tom
8        Ted
9        Terry
10       Tod
11       Stan
12       Steve
13       Sam
14       Sid
15       Sal

Then you would use a UNION query.
The basic form would be:

SELECT N_ID, MOC1 AS DEF FROM tblAttendees
UNION
SELECT N_ID, ABC1 FROM tblAttendees
Union
SELECT N_ID, XYZ1 FROM tblAttendees

BTW, You can't create a UNION query in the query designer. You have to type in the SQL in SQL view..

[June7]

UNION as Steve suggests but the output will be like:

N_ID	DEF
1	Joe
2	Jon
3	Jim
4	Jake
5	John
1	Tim
2	Tom
3	Ted
4	Terry
5	Tod
1	Stan
2	Steve
3	Sam
4	Sid
5	Sal

[ssanfu]

Thanks June.

I was cutting and pasting and I think I had my brain in neutral.


I appreciate everyone looking over my shoulder and correcting the errors that creep in.

[tmpgovrel]

Thank you so much for all of your help! Really appreciate it

--Ally

[tmpgovrel]

Hey all - one more question:

I entered the code into a query through SQL and the list that I am receiving is not populating with names, it is populated with random numbers.

The SQL code that I have used is:
SELECT DISTINCT MOC1 FROM Registration
UNION
SELECT DISTINCT MOC2 FROM Registration
UNION
SELECT DISTINCT MOC3 FROM Registration;

Output that I am receiving:

I have included photos of the relationship between tables, as well as the output of the SQL.

Thanks,
--Ally

[June7]

Random? I doubt it. Those numbers are surely the values of the MOC1, MOC2, MOC3 fields.

The relationship between Registration and Congress113 appears wrong. mocID is the primary key in Congress113 and this is probably the value saved in the MOC fields. The link should be between the primary and foreign key fields.

If you want to see names then will have to include Congress113 in query that joins tables.

You can join Congress113 in each line of the UNION or use the UNION in another query that joins to Congress113.

SELECT DISTINCT MOC1, [display name] FROM Registration INNER JOIN Congress113 ON Registration.MOC1 = Congress113.mocID.

[tmpgovrel]

You have all helped so much! Thanks

[June7]

I forgot to include the [display name] field in the revised SQL. Review edited previous post.