Redisplay records as single item list

[imaginal]

I can currently write a query that will display a list of items and their order like this:

1|B
1|C
2|B
3|A
3|B
3|C

I want to write the query so it displays the data like:

1|B,C
2|B
3|A,B,C

I'm having trouble wrapping my mind around it. Any hints?

[Seth Schrock]

I believe that a crosstab query would do what you want. You can also use the ConcatRelated() function from Allen Browne.

[hvsummer]

I think you can use multivalue field to do this.
create new table with whatever field you want
choose the field that you want those multivalue put in
change datatype to lookup winzard, follow the instruction, choose the right field (at this example is the 2nd field of table example), at the end of step, the step that you can't go "next", only 1 option is "finish"
you will see the option "allow multi value"
ta da!!! soup is on table...

[Seth Schrock]

Per Normalization Rules, multi-value fields shouldn't be used. While it might solve this immediate problem, other problems will come.

[hvsummer]

rule created to make sure you can organize it well, but if you need to store extra description, you have to use multi value. you can use some delimiter like comma, dot..ect. And you can call those value back with split(field, delimiter) in vba.
this call compressing information process.

edit: sometime face the problem to know better way to resolve it better than do as everyone say and limit ur creative.

[Seth Schrock]

You never HAVE to use multi-value fields. Neither MySQL nor SQL Server even offer a multi-value field data type (both enterprise level database engines). That is the purpose of one-to-many relationships.

[jforbes]

I would agree with Seth's first post as being the best way of doing this. There maybe times when stuffing all the information into a single field would be of a benefit, but there aren't very many of them. It is definitely not the first place to start, especially when Access is a Relational Database.