Solved Combine Fields without Duplicates (1 Viewer)

jack555

Member
Local time
Today, 17:10
Joined
Apr 20, 2020
Messages
93
How could we combine values in columns excluding duplicates (column-wise) and blanks? Duplicate check on column-wise only, not row-wise. Below is an example scenario.

Please advise how we can achieve this. Sequencing of combined items not much important. There may be a preferred sequence to get, however not a showstopper or mandatory.

Thanks in advance.

Note: simplified the initial question to avoid confusion.

A​
B​
C​
D​
DesiredOutcome​
AppleOrangeAppleApple;Orange
OrangeOrangeOrange
MangoAppleMango;Apple
OrangeMangoMangoAppleOrgane;Mango;Apple
 
Last edited:

Ranman256

Well-known member
Local time
Today, 09:10
Joined
Apr 9, 2015
Messages
4,339
make a table with keyed field: DesiredOutome.
append your example above to the table. No duplicates will be created.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 13:10
Joined
Feb 19, 2013
Messages
16,555
this is unnormalised data so any solution will be complex. use a formula along the lines of

Outcome:nz(A,"") & iif(nz(B,"")="" or nz(B,"") = nz(A,""),"",";" & nz(B,"")) & iif(nz(C,"")="" or nz(C,"") = nz(A,"") or nz(C,"") = nz(B,""),"",";" & nz(C,"")) & iif(nz(D,"")="" or nz(D,"") = nz(A,"") or nz(D,"") = nz(B,"") or nz(D,"")= nz(C,""),"",";" & nz(D,""))

this is freetyped so may have errors

if data was normalised, as Ranman says it would be much simpler
 

jack555

Member
Local time
Today, 17:10
Joined
Apr 20, 2020
Messages
93
make a table with keyed field: DesiredOutome.
append your example above to the table. No duplicates will be created.
Thank you. The DesiredOutcome column may have duplicate records. Duplicate values need to be checked in the columns and combined. How can we do that? Thanks in advance.
 

mike60smart

Registered User.
Local time
Today, 13:10
Joined
Aug 6, 2017
Messages
1,899
Thank you. The DesiredOutcome column may have duplicate records. Duplicate values need to be checked in the columns and combined. How can we do that? Thanks in advance.
Best to normalise the data so that the values are Records in a Table rather than Fields in a Table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:10
Joined
May 7, 2009
Messages
19,175
see this demo.
create a function that will combine your columns (see Module1).
see qryDesiredOutput on design view, how the function is called from
this query.
see qryDesiredOutput on datasheet view to see the result.
 

Attachments

  • dup_in_db.accdb
    448 KB · Views: 80

jack555

Member
Local time
Today, 17:10
Joined
Apr 20, 2020
Messages
93
see this demo.
create a function that will combine your columns (see Module1).
see qryDesiredOutput on design view, how the function is called from
this query.
see qryDesiredOutput on datasheet view to see the result.
Exactly this is what I was looking for. Works like a charm. The normalisation may be ideal, but my scenario is a little tricky due to existing tables and forms.

@arnelgp you are a life saver always. Big thank you.
 

Users who are viewing this thread

Top Bottom