Does anyone know why the query below would return numbers? I'm
attempting to produce a report that shows in *one* column the names
that appear in multiple columns in a single table.

Table Name: project
col1 = project_id
col2 = manager
col3 = participant1
col4 = participant 2
col5 = participant 3

Query:

SELECT [project].[manager]
FROM [project];

UNION

SELECT [project].[participant1]
FROM [project];

UNION

SELECT [project].[participant2]
FROM [project];

UNION

SELECT [project].[participant3]
FROM [project];

Instead of a single column of names, I'm receiving numbers. What's
really weird is that on first run, the query produced names. On its
second run, however, the query started reporting numbers only.


jim

On Wed, 23 Apr 2008 20:01:48 -0700 (PDT), jim <jimmorgan76 [at] gmail.com>
wrote:

I think you're saying that when you look at the table data, you see
participant names, not numbers, yet in the query you see numbers.

More than likely these numbers are the ParticipantID values
corresponding to the names, and they are found in the Participants
table.
Then in the table design, select the participant1 field, and select
the Lookup tab in the properties below. Set "Display Control" to
Textbox, and you'll have your numbers back.
Personally I always want to deal with the real data, so I NEVER use
the other options. If I want to see the data that way, I will create a
query.

-Tom.


>Does anyone know why the query below would return numbers? I'm
>attempting to produce a report that shows in *one* column the names
>that appear in multiple columns in a single table.
>
>Table Name: project
>col1 = project_id
>col2 = manager
>col3 = participant1
>col4 = participant 2
>col5 = participant 3
>
>Query:
>
>SELECT [project].[manager]
>FROM [project];
>
>UNION
>
>SELECT [project].[participant1]
>FROM [project];
>
>UNION
>
>SELECT [project].[participant2]
>FROM [project];
>
>UNION
>
>SELECT [project].[participant3]
>FROM [project];
>
>Instead of a single column of names, I'm receiving numbers. What's
>really weird is that on first run, the query produced names. On its
>second run, however, the query started reporting numbers only.
>
>
>jim

Brilliant! You were right about the ID. This is actually a query
against a SharePoint list, so I had to join to the built-in User
Information List and return the value from name field corresponding to
"ID." This worked perfectly.

Thanks very much.


Jim