How to split csv field, and get data from another able with those valu 1

[klaas01]

Hello, I have the following question:
There are 2 tables:
- employees
- experiences

a employee can have more than one experience, the keys of those experiences are being stored in the field experiences of the table employee like this: 1,2,3,4

What i want is to show the descriptions of each experience in a field on a form. These descriptions are stored in the table experiences.

So first the field experiences in the table employees should be split to get the keys of the table experiences.

After that the correspondenting values for each key have to be found in the table experiences and put together in a unasigned field on the form.

Does anybody knows how to achieve this?

 

[Golom]

Have a look at faq701-6293 that describes a process for creating a normalized table from your un-normalized, multi-valued fields. Once you have that table then the rest should be a straight-forward SQL join.

 

[klaas01]

Thanks a lot! This is axactly what I needed.
The only remaining question i have left is:

Is there a sql solution to group the multiple results for each employee into one field?
Like this:

Before
employeeid experiences
1 A
1 B
1 C

After
employeeid experiences
1 A,B,C

 

[Remou]

This FAQ by dhookom shows how to concatenate:

http://www.tek-tips.com/faqs.cfm?fid=4233

 

[klaas01]

Hi when I run this sql FAQ701-6293: Parsing Delimited Fields i often get the message "invalid procedure call", when i empty all the tables and restart Access the query works again.

Does anybody knows why access sometimes comes up with this "invalid procedure" message?