Merge multiple records in a field, based on criteria?

[GD]

Is there an easy way (i.e. not TOO much code) to merge multiple records in a
field, based on criteria in another field in the same table?

Sample table
LocNo Name
100 John
100 Jay
101 Kevin
102 David
102 Kelly
102 Mark

What I need is to add a 3rd field that would merge all names that have like
location numbers:

LocNo Name MultName
100 John John; Jay
100 Jay John; Jay
101 Kevin Kevin
102 David David; Kelly; Mark
102 Kelly David; Kelly; Mark
102 Mark David; Kelly; Mark

Any help?? THANKS!!

 

[Philip Herlihy]

The only way I can think of to do this is to use a recordset. Essentially
you run a query in VBA code and "capture" the output as a recordset, which
you can then manipulate using a set of recordset functions. It's not that
hard (although I'm not going to write the code for you!). Have a look here:
http://www.devdos.com/vb/lesson4.shtml
and also here:
http://allenbrowne.com/ser-29.html

Be sure to include error-handling code if you don't want nasty surprises:
see:
http://allenbrowne.com/ser-23a.html
(the first nine numbered lines are enough in most cases).

However, while it's perfectly ok to generate concatenations dynamically from
fields in your database, it's perfectly barbarous to *store* data like this.
Database design is founded on a number of essential principles, of which the
first three "normal forms" are considered pretty essential. If you store
information which has "repeating values", your design will fail the very
first of those "normalisation" principles. This will have the effect of
making some things you try to do later so damn difficult that your hair will
turn grey and fall out, and attractive persons of your preferred gender will
shun your bloodshot eyes. Think of your genetic legacy before you consider
this path.

Phil, London

 

[Arvin Meyer [MVP]]

Have a look at this solution:

http://www.mvps.org/access/modules/mdl0004.htm