Songle row one to many (1 Viewer)

rgwfly

Registered User.
Local time
Today, 01:48
Joined
Jun 7, 2016
Messages
49
Hello,
I have two tables in a one to many relationship.
tbl_Test & tbl_tech
I may have several Tech's working on one test.
I was trying to get a query where I have one record from tbl_Test
with the tech ID's show in a single text field.
For instance:
tbl_test ID tbl_tech
100 W1
100 W2
101 W5
101 W6
102 W7

Results:
100 W1,W2
101 W5,W6
102 W7,

I found some code but it involves creating temp tables. I did not want to bloat my database. Suggestions?
Thanks again
 

June7

AWF VIP
Local time
Today, 00:48
Joined
Mar 9, 2014
Messages
5,423
Use of temp table doesn't have to bloat database. The temp table can be permanent but the records are temporary. I use temp tables and bloating is not an issue.

However, I agree that Allen Browne code should serve your requirement.
 

rgwfly

Registered User.
Local time
Today, 01:48
Joined
Jun 7, 2016
Messages
49
OK, I did use the code from Allen Browne above.
I used in a SQL query as mentioned using

SELECT tbltechID.WeldID, ConcatRelated("WelderID","tblTechID","WeldID = " & [WeldID]) AS Expr2
FROM tbltechID
GROUP BY tbltechID.WeldID;

I keep getting a Error "Syntax error (missing operator) in query expression".
However it does run.
I would like to be able to get this in a simple select query but cannot get the argument to work.
"Maybe its my bad grammar as noted in the post heading"
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:48
Joined
Sep 21, 2011
Messages
14,037
I *think* you might have to qualify the criteria with the table name?
Code:
SELECT tbltechID.WeldID, ConcatRelated("WelderID","tblTechID","WeldID = " & [tbltechID].[WeldID]) AS Expr2
FROM tbltechID
GROUP BY tbltechID.WeldID;
 

June7

AWF VIP
Local time
Today, 00:48
Joined
Mar 9, 2014
Messages
5,423
Tested. No, table qualifier not required. That is such a simple SQL, baffling why you would get that error and the query still run. I don't see anything wrong with it. Perhaps it is actually the VBA procedure that is throwing the error. Step debug the code. The function can be called from textbox.

WeldID is a number field?
 

rgwfly

Registered User.
Local time
Today, 01:48
Joined
Jun 7, 2016
Messages
49
Still get the error.
Something in my group by. if I remove it runs OK.
 

rgwfly

Registered User.
Local time
Today, 01:48
Joined
Jun 7, 2016
Messages
49
Still get the error.
Something in my group by. if I remove it runs OK.

Edit:
OK I think I have it, there was a null field in the underlying table. I added a where clause and it runs without the error. Funny in the original table I don't see the null value. :eek:
 

rgwfly

Registered User.
Local time
Today, 01:48
Joined
Jun 7, 2016
Messages
49
OK seems to work in a independent query.
I am using this for a form with a text box search. I noticed the form slowed significantly using Allen Brownes VBA.
If I create a table and run the query off of that it runs much better. I now just have to figure how to update the table on form loads and event changes.
 

Users who are viewing this thread

Top Bottom