combining values

[ck_heron]

I am pretty new to access and struggling with a query problem. I currently have a table containing data like:

Name_____________Color Chosen
Bill..............................Red
Bill..............................Blue
Tom............................Red
Mark...........................Green
Bill..............................Green
Tom............................Blue

And I would like to be able to get to

Name______________Colors Chosen
Bill..............................Red, Blue, Green
Tom............................Red, Blue
Mark...........................Green

Can anyone suggest a query to get me this second table. Theoretically the maximum number of Colors that one person can choose is 4.

Many thanks in advance

Chris

 

[nst]

A person (e.g. Bill) can have more than one colours chosen.
A color can be chosen by more than one person.

In Relational Databases this is called a many-to-many relation. You need a third table to represent what you want and a bit of ...theory learning to understand it.

The limit of 4 color can be applied through means of dynamic systems, using VB or other appropriate langs.

 

[ck_heron]

nst - thanks for the response.

Firstly, the limit of 4 does not need enforcing within the database, it was just for information.

Secondly, I guess my example was a little inaccurate in retrospect - apologies - totally my mistake

More accurate would be a list of authors and titles of books written. It is theoretically possible that 2 people wrote books with the same title, but not a problem. There will be no table of books indexed by title etc

So my example whould really be more like

Name_____________________Title
Tolkein................................The Hobbit
Tolstoy................................War and Peace
Adams.................................Watership Down
Adams.................................Plague Dogs
Tolkein................................Lord of the Rings

and what I would like to get from my query is


Name_____________________Titles written
Tolkein................................The Hobbit, Lord of the Rings
Tolstoy................................War and Peace
Adams.................................Watership Down, Plague Dogs

Titles will not be keys into any other tables.

Many thanks in advance, and thanks for your patience

 

[KenHigg]

Chris, The only way I have found to do this is rather involved as it involves having a temp table that stores the resulting data. Here is a link to an example I did for someone else:

Link

 

[ck_heron]

(KenHigg is so smart!)

Ken - I will have a look in some detail and see if it solves my problem! Many thanks.

Chris

 

[KenHigg]

(KenHigg is so smart!)

Just to see if anyone really ever hit the command button

Ken - I will have a look in some detail and see if it solves my problem! Many thanks.

Chris

Cool - Glad to help