Query generating more than 1 identical record

[Domino2]

I have a VB query that starts off with:

mysql = "SELECT StrConv(Concatenate(""SELECT DISTINCT Company FROM qryJC WHERE ID="" & [Business].[ID]),3)"

ie there are other fields that follow above line.
However I think the query contained qryJC keeps running the above mysql query each time it concantenates the field company.

The query qryJC is:
SELECT Junction.ID, Companies.Company AS Expr1
FROM Companies INNER JOIN Junction ON Companies.CompanyID = Junction.CompanyID;

Can anyone confirm this is the case, I think it is as the same number of records which are identicle are related to the number of records being dealt with in concanenation.

Thanks

 

[lespaul]

why not this:

mysql = "SELECT StrConv(Concatenate(""SELECT DISTINCT Company FROM [b]JUNCTION[/b] WHERE ID="" & [Business].[ID]),3)"

Leslie

Have you met Hardy Heron?

 

[Domino2]

Thanks, however it still produces duplicates. I have checked the tables and there is only i with ID=2004, it has 4 company names, so it produces 4 records exactly the same.

 

[dhookom]

Provide the total SQL statement that incudes the Concatenate function. I expect your query includes the child/junction table that is shouldn't.

Duane
Hook'D on Access
MS Access MVP

 

[Domino2]

I have put the DISTINCT statement in the front ie DISTINCT strConv etc and that has fixed it. Thanks