Using a multivalued field

[Triedtwice]

Does anyone know if I can add a multivalued field to an existing table so that my query will combine into one field the many results from a 1:many relationship?

I have a table (Sites_Tab) with values of Site_1, Site_2, etc. A relationship exists with another table (Value_Integration_Tab) that has values of VI.1, VI.2, etc. Site_1 will have one or more VI associated with it. My query shows a separate row for each occurrence:

Site_1 VI.1
Site_1 VI.4
Site_1 VI.7
Site_2 VI.1
etc...

I would like to have the results of the query appear like this:

Site_1 VI.1, VI.4, VI.7
Site_2 VI.1
etc...

Is that possible using a multivalued field in one of these 2 tables? If so, how? Thank you for you replies.

 

[xenou]

I don't know but my guess is that an m-v field would behave the same anyway. It's not meant for displaying data but for handling relational data (under the hood, it is nothing more exotic than related records just as you have already).

I'm not sure what the best solution is. What you are describing is very unnatural for query languages - sort of like walking on your hands when you could be running on your feet instead. It might be possible to use a pivot, but that depends on the data. Can you give more detail and some sample data.

 

[Triedtwice]

Yes, that's what I was afraid of. However, your pivot table suggestion has supplied me with an adequate work-around...thanks!!!

 

[xenou]

Great. Good luck and post back if there are more questions.
ξ