query to group names from multiple fields

[rush2112]

I have a table with 7 columns and 50 rows.
The first column is a unique ID#, the other 6 columns are for names. (Name1, Name2, ......Name6).
I need to create a 2 column query to extract the six names that belong to each unique ID. It doesn't matter that the ID will be repeated multiple times in the same column, I'll deal with that later. I just need to have the query results with an ID# column and a single Name column.

Any help would be appreciated.

[Killer42]

What sort of setup are we talking about? VB? What version? By "table" are you referring to a database table? What sort of database?

[rush2112]

Yes, it's an Access 2002 database table. I'd like it to be just a standard select query if possible, that I can use in creating a report. (It will actually be used as part of a cascading set of queries).

[Killer42]

Yes, it's an Access 2002 database table. I'd like it to be just a standard select query if possible, that I can use in creating a report. (It will actually be used as part of a cascading set of queries).

In that case I'm going to move this over the the Access forum, as you'll find much more Access expertise there.

[Jim Doherty]

Yes, it's an Access 2002 database table. I'd like it to be just a standard select query if possible, that I can use in creating a report. (It will actually be used as part of a cascading set of queries).

Now this is by no means the only way to do this, neither is it as efficient as it could be. I have done it for you for 'clarity' rather than technical excellence... it merely gives you a very simple insight into calling a function, looping using an array and returning the result of that function to the SELECT query that called it.

Assumptions
Going by your example lets say you have a table called tblRush and fields names called ....Name1,Name2 ,Name3,Name4,Na me5,Name6
(This function is not concerned with your ID column as that is merely your row reference and not vital to this function)

Paste this fully commented function below into a module and save it

 
'This function takes the names of the six fields passed in
'and returns a concatenated string of values as the result
'Typical SQL syntax to use this example for a table called tblRush will be:
 ' SELECT tblRush.ID, fn_RushNames([Name1],[Name2],[Name3],[Name4],[Name5],[Name6]) AS Available_Names
 ' FROM tblRush;
 
' You obviously need to amend the fieldnames and table name to suit you
 
'define an zero based array consisting of the six variant variables passed in
    Dim RushArray(5)
    RushArray(0) = n1
    RushArray(1) = n2
    RushArray(2) = n3
    RushArray(3) = n4
    RushArray(4) = n5
    RushArray(5) = n6
'loop through the elements of the array until the upper bound limit
'concatenatating each value and assigning a trailing comma
'skip over where a column might be null by converting to zero
'and testing for the zero value
    For i = 0 To UBound(RushArray)
        If Nz(RushArray(i), 0) = 0 Then
            fn_RushNames = fn_RushNames
        Else
            fn_RushNames = fn_RushNames & RushArray(i) & ","
        End If
    Next i
'By now we have a string of values if at least one cell has a value
'separated by a comma so we strip off the trailing comma and return the result
'In the event of no values at all in any of the cells we test for a zero length
'string and return a value stating there are no values in ANY of the fields for
'that particular row
If fn_RushNames = "" Then
    fn_RushNames = "No values available for any or the six columns"
Else
    fn_RushNames = Left(fn_RushNames, Len(fn_RushNames) - 1)
End If
End Function
 

Like I mentioned it is not the only way to do it. You can concatenate directly in the grid without using this function at all and test for nulls etc but I'll leave anyone else to give you their examples.

Regards

Jim