removing duplicates in a query (1 Viewer)

[MilaK]

Hello,

I'm running the following queries to concatenate values from a table. The query is working fine, however, sometime it returns duplicate values that I would like to remove. I've tried using "select distinct" with SQL statement but it gave me an error. Could someone please suggest how to remove duplicate values from the "amplicons" field, preferably in the existing code below. Here is an example of the output:

AKT1 exon (3), ALK exon (25), ALK exon (25), ALK exon (23), ALK exon (22), ALK exon (21), AR exon (6), AR exon (8), BRAF exon (11), BRAF exon (11), CDK4 exon (2)

Here is the code:

Dim rsI As DAO.Recordset
Dim rsO As DAO.Recordset
Dim rsU As DAO.Recordset
Dim SQL As String
' init table

Set rsI = CurrentDb.OpenRecordset("Select Distinct sample_id from tbl_Samples Where run_name = '" & Me.run_name & "'")

Debug.Print Me.run_name
Set rsO = CurrentDb.OpenRecordset("tbl_failed_amps_report")
 
While Not rsI.EOF
    rsO.AddNew
    rsO!sample_id = rsI!sample_id

   SQL = "SELECT [tbl_failed_amplicons].[gene] & ' exon (' & [tbl_failed_amplicons].[Exon] & ')' AS expr"
    
                SQL = SQL & " FROM tbl_Samples INNER JOIN tbl_failed_amplicons ON tbl_Samples.sample_id = tbl_failed_amplicons.sample_id"
                
                
                SQL = SQL & " WHERE (((IIf([gene_panel]<>'Whole Panel',InStr([gene_panel],[gene])>0,' '))=True) AND ((tbl_failed_amplicons.sample_id)= '" & rsI!sample_id & "'))"
               
                
    Set rsU = CurrentDb.OpenRecordset(SQL)
    While Not rsU.EOF
    If IsNull(rsU![Expr]) = False Then
        rsO!amplicons = rsO!amplicons & ", " & rsU![Expr]
        
    End If
    'Debug.Print rsO!amplicons
   
    rsU.MoveNext
Wend
' remove leading ", "
rsO!amplicons = Mid(rsO!amplicons, 3)
rsO.Update

rsI.MoveNext

Wend

Thanks,

Mila

 

[Ranman256]

in the query property : set UNIQUE VALUES = TRUE.

in sql :
SELECT DISTINCT

 

[MilaK]

"Select distinct" returns an error:

Run time error '3093'
Order by clause tbl_failed_amplicons.sample_id conflicts with DISTINCT

Thanks

 

[arnelgp]

from Which part from the code
are you getting duplicate?

is it on rsU![Expr] ?

if so add another string Variable:

Dim strExpr As String


now insert this variable to your code:

...
...
While Not rsU.EOF
	If IsNull(rsU![Expr]) = False Then
		If Instr(strExpr, rsU![Expr] & ",") = 0 Then
			rsO!amplicons = rsO!amplicons & ", " & rsU![Expr]
			strExpr = strExpr & rsU![Expr] & ","
		End If
	End If
	rsU.MoveNext
Wend
...
...

 

[MilaK]

Ok, I get it. This is great! Huge thanks

Mila

 

[arnelgp]

here is the logic.

when we loop through each
record in rsU recordset,
whe check if rsU![Expr] is in
our new string variable strExpr:

Instr(strExpr, rsU![Expr]) = 0

0 means it is not found in strExpr.
If not found then we add rsU![Expr] to
concatenate string.
at the same time we add rsU![Expr] to our
string variable strExpr.

next record comes and we check again,
if it is in the variable we just
move on to next record, otherwise
concat it and add it to strExpr.

 

[MilaK]

Do you know if there is a limit to strExpr string size, like 255 characters?

thanks

 

[arnelgp]

More or less 65,000 char

 

[MilaK]

please help to figure out why your code removing other values beside the duplicates.

Here are two outputs with the original code and the modified code:

My code:
AKT1 exon (3), ALK exon (21), ALK exon (22), ALK exon (23), ALK exon (24), ALK exon (25), AR exon (6), AR exon (8), BRAF exon (11), BRAF exon (15), CDK4 exon (2), CTNNB1 exon (3), DDR2 exon (5), EGFR exon (3), EGFR exon (7), EGFR exon (12), EGFR exon (15), EGFR exon (18), EGFR exon (19), EGFR exon (20), EGFR exon (21), ERBB2 exon (8), ERBB2 exon (17), ERBB2 exon (18), ERBB2 exon (19), ERBB2 exon (20), ERBB2 exon (21), ERBB2 exon (22), ERBB3 exon (2), ERBB3 exon (3), ERBB3 exon (6), ERBB3 exon (8), ERBB3 exon (9), ERBB4 exon (18), ESR1 exon (9), FGFR2 exon (7), FGFR2 exon (8), FGFR2 exon (9), FGFR2 exon (12), FGFR2 exon (14), FGFR3 exon (7), FGFR3 exon (9), FGFR3 exon (14), FGFR3 exon (16), GNA11 exon (4), GNA11 exon (5), GNAQ exon (4), GNAQ exon (5), HRAS exon (2), HRAS exon (3), IDH1 exon (4), IDH2 exon (4), JAK1 exon (14), JAK1 exon (15), JAK1 exon (16), JAK2 exon (14), JAK3 exon (11), JAK3 exon (12), JAK3 exon (15), KIT exon (8), KIT exon (9), KIT exon (11), KIT exon (13), KIT exon (17), KRAS exon (2), KRAS exon (3), KRAS exon (4), MAP2K1 exon (2), MAP2K1 exon (3), MAP2K1 exon (6), MAP2K2 exon (2), MET exon (14), MET exon (16), MET exon (19), MTOR exon (30), MTOR exon (39), MTOR exon (40), MTOR exon (43), MTOR exon (43), MTOR exon (47), MTOR exon (53), NRAS exon (2), NRAS exon (3), NRAS exon (4), PDGFRA exon (12), PDGFRA exon (14), PDGFRA exon (18), PIK3CA exon (2), PIK3CA exon (2), PIK3CA exon (5), PIK3CA exon (6), PIK3CA exon (8), PIK3CA exon (8), PIK3CA exon (10), PIK3CA exon (14), PIK3CA exon (19), PIK3CA exon (21), RAF1 exon (7), RAF1 exon (12), RET exon (10), RET exon (11), RET exon (13), RET exon (15), RET exon (16), ROS1 exon (36), ROS1 exon (38), SMO exon (4), SMO exon (6), SMO exon (8), SMO exon (9)

Your code:
AKT1 exon (3), ALK exon (22), ALK exon (24), ALK exon (25), AR exon (8), BRAF exon (11), BRAF exon (15), CDK4 exon (2), CTNNB1 exon (3), EGFR exon (3), EGFR exon (7), EGFR exon (15), EGFR exon (18), EGFR exon (20), EGFR exon (21), ERBB2 exon (17), ERBB2 exon (18), ERBB2 exon (21), ERBB2 exon (22), ERBB3 exon (2), ERBB3 exon (8), ERBB3 exon (9), ERBB4 exon (18), FGFR2 exon (7), FGFR2 exon (8), FGFR2 exon (9), FGFR2 exon (12), FGFR2 exon (14), FGFR3 exon (7), FGFR3 exon (14), FGFR3 exon (16), GNA11 exon (5), GNAQ exon (4), HRAS exon (2), IDH1 exon (4), IDH2 exon (4), JAK1 exon (14), JAK1 exon (15), JAK2 exon (14), JAK3 exon (11), JAK3 exon (12), KIT exon (8), KIT exon (9), KIT exon (11), KIT exon (13), KIT exon (17), KRAS exon (3), KRAS exon (4), MAP2K1 exon (2), MAP2K1 exon (3), MET exon (14), MET exon (19), MTOR exon (30), MTOR exon (39), MTOR exon (43), MTOR exon (53), NRAS exon (2), NRAS exon (3), NRAS exon (4), PDGFRA exon (14), PIK3CA exon (19), RAF1 exon (7), RAF1 exon (12), RET exon (10), RET exon (11), RET exon (13), ROS1 exon (36), SMO exon (4), SMO exon (9)

 

[arnelgp]

I haven't seen your data yet. I guess you have to init srrExpr on the outer loop:

Do while not rsI. Eof
StrExpr=""
...
...

 

[MilaK]

that was it! thank you!!!!