join two tables on multi-valued field

[smudger]

Hi All,

I am trying to join two tables on a multi-valued field and getting the error message:
"The multi-valued field '[linking jobs to people].[linking jobs to people]' is not valid in the specified join clause."



the mysql query (excuse table names and column names) is :
"SELECT [linking jobs to people].[full_name], [linking jobs to people].[linking jobs to people]
FROM [linking jobs to people] INNER JOIN [risks per job] on [risks per job].[jobs]=[linking jobs to people].[linking jobs to people]"

Can a join be performed against a multi-valued field?

Thanks a million,
Smudger.

[RayMilhon]

First I would never use a multi valued field in a join. Don't know if it's possible I Just don't think it's appropriate.

However if you must keep this in mind. When using an Inner join both fields must be equal so I believe if it's a multi-value field each value must match plus they must be in the same order.

[jwill]

I agree w/ Ray, having columns that house multiple values is not a good idea. If you have to combine them to make them useful to you, make them separate columns in the table and then concatenate them in a query. For instance instead of having a field called "Full Name" in a table, make a first name field and a last name field in the table and then concatenate them in a query ([first name]&' '&[last name]) as full_name . You still get what you need while having good structured tables.

[June7]

No, cannot directly join on multi-vaue field. Review http://office.microsoft.com/en-us/ac...001233722.aspx