Link to home
Start Free TrialLog in
Avatar of PeterBaileyUk
PeterBaileyUk

asked on

dedup and concatenate string

I have a table that is populated from various sources called TblReviewCode.

[AutoID], [Mvris Code], [ReviewCause]

autoid is pk, [mvris code] is our code identifier, ReviewCause tells me what error was found and what process picked it up.

I have attached an example from the table where duplication of the [mvris code] has taken place because different processes have picked up the same error.

in ex

3680,  W21KZ, CC[P]
3546,  W21KZ, CC[C]

what I would like is some code that will find the duplicate [mvris code] and concatenate the string so above becomes

newautoid,  W21KZ, CC[P&C]

above only has two dupes but could be 1, 2 or more.
so
3680,  W21KZ, CC[P]
3546,  W21KZ, CC[C]
4588,  W21KZ, CC[V]

=newautoid,  W21KZ, CC[P&C&V]



above only has two dupes but could be 1, 2 or more.
so
3680,  W21KZ, CC[P]
3546,  W21KZ, CC[C]
4588,  W21KZ, CC[V]

=

above only has two dupes but could be 1, 2 or more.
so
3680,  W21KZ, CC[P]
3546,  W21KZ, CC[C]
4588,  W21KZ, CC[V]

not sure how to go about this

the dupe entries would need deleting and the new entry appended am in access 2010 vba
ee-ex.PNG
Avatar of jerryb30
jerryb30
Flag of United States of America image

Is the Review Cause structure absolute? Char, Char, Left Bracket, Char, Right bracket?

Can there be dupes with the same review cause?  CC[C], CC[C]? Does that become CC[C&C]?
Also, is this a one-shot, or on-going process? It would probably be better to make a query which concatenates for review purposes, if on-going. If your PK is important, how will you retain integrity with whatever tables are associated with tblReviewCode if you delete records?
Avatar of PeterBaileyUk
PeterBaileyUk

ASKER

yes each month the table will be emptied once the [mvris code] is corrected so a query that could do it is a good idea.
ASKER CERTIFIED SOLUTION
Avatar of jerryb30
jerryb30
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you!