Auto-populating field with comma separated list based on conditional in another table

[plackowski]

Hypothetical scenario: You have an employee table and an accounts table. Each employee can be working on multiple accounts. How would I go about creating a field in the employee table that lists the accounts they have been assigned to?

Account:	Name	AssignedEmployee
	A	Jim
	B	Dwight
	C	Jim

Employee:	Name	AccountsAssigned
	Dwight	B
	Jim	A,C
	Andy	-

[Edit]: Also, realistically this doesn't have to be a field, it would just need to appear in this format in a final report.

[CJ_London]

look at the concatrelated function to use in your form recordsource

http://allenbrowne.com/func-concat.html

[plackowski]

I added the function, but I'm still not sure how to put that data into a field or a report. I don't have a form set up yet, but even if I did, I would have the form set up in a 'per employee' format. That way there would be room to expand on each of the accounts they are working on with a sub-report.

[plackowski]

So I've worked out how to edit the combo box control source in my report, but I'm also not clear on the formatting for the third argument in the ConcatRelated function.

I think the entire entry for the recordsource should look like this:

=ConcatRelated("AssignedEmployee", "Account", "Name = " & [Name], "[Name]")

But when I run the report I get asked to enter parameter values for 'ConcatRelated' and 'Name'.

[CJ_London]

Name is a reserved word and should not be used as a field name.

you need to look at the parameters you are using. I would have thought it should be

=ConcatRelated("[Name]", "Account", "AssignedEmployee = " & [Name])

You said this was hypothetical, but seems to be the real situation. So I think you are getting ahead of yourself. Before designing a form, you need to make sure the tables work together as you require.

You need to understand how tables work, learn about primary keys and family/foreign keys. Learn about relationships. Get the tables working as you require, then you can build your forms.

I strongly recommend you try a few of the lessons online to get a better understanding

[plackowski]

Ok, fair point, that's a bad example. I'm trying to adapt my question to make it more accessible to everyone on the forum, but I think I'm just confusing myself. Here's my actual intended use. I have a list of cables and a list of conduits. These electrical cables get fed through a series of PVC conduits. Each conduit has a physical location where it starts and stops, be it an electrical panel or a manhole. However, one cable may go through multiple manholes, so I've created a CableRoute table to track the route of each cable through the conduits. Right now I'm trying to get a list of all the cables in each conduit, using the information in this third table.

Conduit	CondName*	CondFrom	CondTo	CondContents (what I'm trying to automatically generate in a report)
	C001	Panel 1	MH 3	435, 436
	C002	MH 3	MH 5	436
	C003	MH 5	Panel 2	436

CondName is the primary key for the Conduit table

Cable	CableNum*	CablePurpose	CableSize	CableGroundSize	CablePath (another thing I'm trying to automatically generate, not really part of this question though)
	435	Control	(2) #12	w/gnd	C001
	436	Power	(2) 250kcmil	4/0 gnd	C001, MH3, C002, MH5, C003

CableNum is the primary key for the Cable table

CableRoute	CableNumF*	Segment*	CondNameF
	435	1	C001
	436	1	C001
	436	2	C002
	436	3	C003

CableNumF and Segment form a 2-field primary key for the CableRoute table
CableNumF and CondNameF are foreign keys (1 to many relationships with the first two tables)

I've then attempted to generate a report with the following Record Source:

SELECT CondName, CondFrom, CondTo, ConcatRelated("CableNumF","CableRoute","CondNameF = " & [CondName]) FROM Conduit;

My earlier issue with parameter values was caused by VBA security permissions, but after resolving that, I now get a generic "Error 3061: Too few parameters. Expected 1." error. I'm sure it has to do with my third parameter, since using ConcatRelated("CableNumF","CableRoute") compiles (it just gives me 435, 436, 436, 436 for every conduit though).

[plackowski]

I need to read more carefully next time. From http://allenbrowne.com/func-concat.html:

If the foreign key field is Text (not Number), include quote marks as delimiters, e.g.:
"[ForeignKeyFieldName] = """ & [PrimaryKeyFieldName] & """"

So the corrected Record Source is the following:

SELECT CondName, CondFrom, CondTo, ConcatRelated("CableNumF","CableRoute","[CondNameF] = """ & [CondName] & """") FROM Conduit;

Thanks for the assistance Ajax!