Concatenate multiple records

[Jeepers321]

These are my table names

ACTIONS
WHORESP
ACTIONEMP

These table are linked and if I was to open the ACTIONS table and clicked on the + for each record I would be able to see every ACTIONEMP for each ACTION. What I am needing is this same data to show up in a text box. I have tried different expressions such as DLookup, as well as queries with the DLookup expression as well. This might need to be done with VBA.
Basically what I am wanting is this:

ActionID |ActionEmp
1 | Tony, Tom, Bill
2 | Tony, Bill
3 | Tom
4 | Bill, Jack

The best I can get so far is this:

ActionID |ActionEmp
1 | Tony
1 | Tom
1 | Bill
2 | Tony
2 | Bill
3 | Tom
4 | Bill
4 | Jack

Any help would be greatly appreciated.

Thanks in advance.

 

[Frink]

Hallo,

You need to write a function to return the list as a string for a given ActionId.
Use a recordset and loop through it, appending to a string variable.

- Frink

 

[Zion7]

or,
use a list box, with a parameter select query, based on the linking field.

SELECT * FROM tblWHORESEP WHERE (((fkActionID) =(Forms!frmAction!pkActionID)))

or a subform, linkChild/Master Fields = the linked fields.

or

Open recordset, using GetString method(similiar to Frink's suggestion)

 

[PHV]

Have a look here:

http://www.tek-tips.com/faqs.cfm?fid=4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[OhioSteve]

If there are only a few action IDs, you can setup a macro with multiple append queries. That method avoids using VBA. If that approach appeals to you, post. I will explain further.

 

[Jeepers321]

The action IDs will continue to grow so the macro method will not work. Thanks anyway OhioSteve

PHV - Thanks for the link I will try that way.

 

[Zion7]

If what you're looking for,
is along the lines of PHV's link,
Why not GetString()?
Even if you wrap it like the link showed, in a User Defined function?
Plus meilleur, n'est-ce pas?

 

[Frink]

Hallo,

I've just looked at GetString as Zion7 suggested.
Looks like that'll do the trick for you. Nice one Zion7, I've learned something new today

Presumably you can set the recordset to return the ActionEmp for the records you want and do a GetString with no Field delimiter, and a "," row delimiter and everyone's happy.

- Frink

 

[Zion7]

Thx for the cudos Frink.

yes, you have very versatile options with the delimiter.
I often use a line feed, vbCrLf, for the rows,
which is actually the default for rows.
Tab is default for columns.