Concatenating produces duplicates in each record.

[AllenK]

When I run this code: SELECT OE_Interchanges_tbl.PART_NO, Concatenate("SELECT
comp_no FROM OE_Interchanges_tbl WHERE part_no ='" & [part_no] & "'") AS OE
FROM OE_Interchanges_tbl I recieve duplicate entries. Example for part
number 106L I have two interchanges ABC, and 123 in my table it shows as
106l ABC
106l 123.
After running the code it concatenates to this:
106l ABC, 123, ABC, 123
It should read like this:
106l ABC, 123
How do I get rid of the duplications?
I only run concatenations once in a while. I believe Duane Hookum gets
credit for the code I am using, and I thank him.

 

[Duane Hookom]

Simply change your query to a totals/group by query. The other option is to
first create a unique query like:
=== qgrpPartNos ======
SELECT OE_Interchanges_tbl.PART_NO
FROM OE_Interchanges_tbl
GROUP BY PART_No;

The use this query
SELECT Part_NO, Concatenate("SELECT
comp_no FROM OE_Interchanges_tbl WHERE part_no ='" & [part_no] & "'") AS OE
FROM qgrpPartNos;

 

[John Spencer]

SELECT OE_Interchanges_tbl.PART_NO
, Concatenate("SELECT DISTINCT comp_no FROM OE_Interchanges_tbl WHERE
part_no ='" & [part_no] & "'") AS OE
FROM OE_Interchanges_tbl

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================