concatination - multiple records to become one field

[kimx]

Hello !

I've got a problem and don't know what to do with it.

In theorie it should be like this:

A table
- tblPersons that holds information about persons.
- tblRoles, holding different roles people can have
- tblPersonRoles as a linktable for the many-to-many persons can have different roles and
roles can be for different persons).

But, in queries, forms etc, I'll never want to see two records for a person, showing each role.
I will always need to see the person and the different roles concatinated in one record.
Especially in forms...

eg. Members of a band: Mr Smith - role: vocals/guitar.

I have no clue if this is possible or not, and if this is going to slow down everything.

The only thing I can see is no many-to-many but to put as well 'vocals' as 'guitar' as 'vocals/guitar'
in my tblRoles table. But this is not really following the rules as there might be many
combinations...

Any suggestions, tips more than welcome !!!

Thanks a lot,

Evelyne

 

[tlbroadbent]

You should use a Cross-Tab query like the following. Hope I typed it in correctly.

TRANSFORM First(a.RoleDesc) AS Role
SELECT a.PersonName
FROM
(Select PersonName, RoleDesc, RoleID
tblPersons As p
Inner Join tblPersonRoles As pr
On p.PersonID=pr.PersonID
Inner Join tblRoles As r
On pr.RoleID=r.RoleID) As a
GROUP BY a.PersonName
PIVOT "Role" & CStr([RoleID]); Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I shall try to correct errors when shown to be errors, and I shall adopt new views so fast as they shall appear to be true views." - Abraham Lincoln

 

[JoeMiller]

I have a concatenation function that does exactly what you're looking for. I have a small sample database I can send you if you provide me with your email address.

Joe Miller
joe.miller@flotech.net