This is simple. I have a table with a "key" and a name:

111    Smith
111    Jones
111     Zebra

222     Carter
222     Ford
222     Clinton

I want to find an SQL statement that will return a recordset that has all of the
names in one row... when passing the parm for the "key":

111    Smith   Jones   Zebra

Is this possible? What is the beginning of the SQL statement that ends

strSQL =  "Select ........ From Table1 Where key = 111"
rst.open strSQL

Thanks to anyone who can help

SELECT TOP 1 test.key, test.Name, test_1.Name, test_2.Name
FROM (test INNER JOIN test AS test_1
     ON test.key = test_1.key)
     INNER JOIN test AS test_2
     ON test.key = test_2.key
WHERE test_1.Name<>test.Name
     AND test_1.Name<>test_2.Name
     AND test_2.Name<>test.Name
     AND test_2.Name<>test_1.Name
     AND test.key=111;

But if, as is more likely, you have a varying number of entries, then
querying in this manner would require some code to build the sql.

However, something you could experiment with is adding another field with a
unique identifier for each record such as an autonumber, so that you have:

Table: test
ID Key     Name
----------------
1  111     Smith
2  111     Jones
3  111     Zebra
4  222     Carter
5  222     Ford
6  222     Clinton

Then, assuming you are using a database that supports the syntax (or
something equivalent), you could use:

TRANSFORM First(test.Name) AS [The Value]
SELECT test.Key
FROM test
WHERE test.Key = 111
GROUP BY test.Key
PIVOT test.ID;

This would give you the result that you desire.

HTH,
Richard

This worked great!!! I get:

Key         1                         2                     3
111         Smith                Jones               Zebra

Is there a way where I can the column names as something like "Name1", "Name2"
etc.

It's not a big deal, but it might help for this app. I'm working on.

I really appreciate the help .

Al

If there is another piece of unique (per key) data relating to each of the
records in your table that you can add, you could use that for a heading. As
an example, suppose your table was recording players on a team. You could
then add a "Position" column (note that in this case, Key would refer to the
name of the team):

Key  Position           Name
---------------------------
111  Right Wing         Smith
111  Left Wing          Jones
111  Centre Forward     Zebra
222  Goalkeeper         Carter
222  Centre Half        Ford
222  Left Wing          Clinton

Changing the PIVOT line to read: 'PIVOT test.Position' would give you the
Position as a column header.

If something like this doesn't suit your data, you could simply change the
PIVOT line to read:

PIVOT "Name" & test.ID

But then using Key=222 would give headings of "Name4", "Name5", "Name6",
which is probably not too useful.

One way around that would be to change the autonumber field to a number
field and have the field filled in automatically during data entry using
some code that looks up the last number used by that key and returns the
next one in sequence.  Then your data would look like:

ID Key     Name
----------------
1  111     Smith
2  111     Jones
3  111     Zebra
1  222     Carter
2  222     Ford
3  222     Clinton

and you could use the method above to give you column headings of "Name1",
etc.