combining multiple records to 1

[moernoo]

Hey,

I have the following problem: (in fact it's a bit more complicated but i've tried to simplify it in this example)

i've a table with users that gives every user an unique ID. i want to give each user multiple properties. i want these properties chosen from another table.

this is the structure i had in mind:

TblMain:
ID | Name |
------------------
1 | Jos
2 | Jef

TblProperties:
ID | PropertiesID
----------------------------
1 | 1
1 | 2
2 | 1

TblProperties:
PropertiesID | Properties
------------------------------------------
1 | Tall
2 | Bold


so in this example:
- user 1 = Jos = Tall and bold
- user 2 = Jef = Tall

My purpose: getting a table like :
Name| Properties
----------------------------------
Jos | Tall + Bold
Jef | Tall

Does somebody knows how to do this? or has a better way of structuring my tables?

thanks very much !!!

 

[fneily]

See:
Fundamentals of Relational Database Design

http://r937.com/relational.html

"My purpose: getting a table like :
Jos | Tall + Bold

You don't want a table like above. Your three main tables are correctly normalized. You would then run a query to get the structure you want.

See:
faq701-4233

 

[moernoo]

Thank you for your reply !

that was exactly what i needed, i did not know such custom functions where possible in access !


thanks