Merge multiple records in a field, based on criteria?

  • Follow


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!!

-- 
GD
0
Reply Utf 12/18/2009 3:43:01 PM

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

"GD" <gd@discussions.microsoft.com> wrote in message 
news:78709F84-27C7-4F40-888A-A70EA59B464F@microsoft.com...
> 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!!
>
> -- 
> GD 

0
Reply Philip 12/18/2009 7:03:18 PM

Have a look at this solution:

http://www.mvps.org/access/modules/mdl0004.htm
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"GD" <gd@discussions.microsoft.com> wrote in message 
news:78709F84-27C7-4F40-888A-A70EA59B464F@microsoft.com...
> 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!!
>
> -- 
> GD 


0
Reply Arvin 12/18/2009 7:20:17 PM

2 Replies
850 Views

(page loaded in 0.039 seconds)


Reply: