Split A String In A Query

[Twistedmaw]

I'm a noob when it comes to MS Access and I hope someone can help me.

I'm working on a pie chart report that is supposed to show each members who worked for several projects. Here's the table:

Project Workers
1 Smith
2 Coles
3 Smith
4 Smith
5 Milford, Coles
6 Smith,Coles,Jacobson
7 Coles
8 Lee
9 Milford
10 Lee, Jacobson

When I generate a report, the pie chart doesn't seem to show one member per slice for those projects with more than 1 worker (rows 5, 6, 10). Obviously, it's because the some of the data entered under the Workers field have multiple names.

I'm trying to make a query that can separate the names but failed. I also tried the trim functions but it requires a specific length. My boss (who is the one who enters the names) sometimes misses out in putting a space after a comma like in row 6 of the table - and this makes it harder, I guess, to filter out the names.

I tried to search online but I could not find any topic close to this. Any suggestions that would resolve this problem?

 

[plog]

You need to store your data correctly. If multiple people can work on a project, then its time for a new table to do that. All those comma seperated names need to go alone into their own field so they can be reported on.

You can't do what you want via just a query. You weren't specific, but my guess is you want a new row of data for each name in Workers. For example:

6 Smith,Coles,Jacobson

Would become this:

6 Smith
6 Coles
6 Jacobson

Nope, you can't just create records out of thin air. Even if you wanted this:

Project; Worker1; Worker2; Worker3; Worker4; Worker5
6; Smith; Coles; Jacobson; ; ;

That's not possible to have a dynamic amount of columns. So in short, fix your table structure.