Concatenate multiple answers into one field

[lmcc007]

First, let me say I may not be asking the correct question or the correct way. But, I have an unbound field that I want to populate with multiple answers into one. I want all the subsidiaries, aka, and fka to become one in to an unbound field separate by a semicolon. That is:

ABC
ABC123
ABCxyz
​

should be look like this in the unbound field:

ABC; ABC123; ABCxyz​

In the query I put:

Cross Reference: [CompanyNameTitle] & IIf(IsNull([Type]),"",", " & [Type])

How do I get it to loop or continue until the last record?

 

[vbaInet]

Have a look at this generic function from Allen Browne:

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

Just a side note, it's much better for you to ask the question first and if the helper feels it's necessary to see your db in order to solve the problem, then please do so.

 

[lmcc007]

Have a look at this generic function from Allen Browne:

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

Just a side note, it's much better for you to ask the question first and if the helper feels it's necessary to see your db in order to solve the problem, then please do so.

Thanks vbaInet,

That's what I am looking for! But, I have a problem. I posted to ControlSource:

=ConcatRelated("CompanyNameTitle","tblCompanyName","CompanyID = " & [CompanyID])

then I get:

Error 3061: Too few parameters. Expected 1.​

Any ideas what I'm doing wrong?

 

[vbaInet]

Is CompanyID Text or Number?

 

[lmcc007]

Is CompanyID Text or Number?

It is Number.


This is what I did--my first junction table--been reading following examples given:

tblCompany

CompanyID dbLong PrimaryKey Indexed
CompanyDateEntered dbDate
CompanyWebsite dbText
CompanySize dbText
CompanyProduct dbText
CompanyYearFounded dbLong​

tblCompanyCompanyNameJunction

CompanyID dbLong PrimaryKey ForiegnKey Indexed
CompanyNameID dbLong PrimaryKey ForiegnKey Indexed
Type dbText
DefaultForCompany dbBoolean​

tblCompanyName

CompanyNameID dbLong PrimaryKey Indexed
CompanyNameTitle dbText Indexed​

Relationships are:

Name: tblCompanyNametblCompanyCompanyNameJunction

Table: tblCompanyName
Foreign Table: tblCompanyCompanyNameJunction
PK: CompanyNameID FK:CompanyNameID​

Name: tblCompanytblCompanyCompanyNameJunction

Table: tblCompany
Foreign Table: tblCompanyCompanyNameJunction
PK: CompanyID FK:CompanyID​

 

[vbaInet]

Upload the updated db so I can have a quick look.

 

[lmcc007]

Upload the updated db so I can have a quick look.

here it is

 

[vbaInet]

Ok, the table you're using has nothing for the function to work on. That is, there are no records with similar ID. You need to build a query that will pull from the tables in concern for you to have related records.

 

[lmcc007]

Ok, the table you're using has nothing for the function to work on. That is, there are no records with similar ID. You need to build a query that will pull from the tables in concern for you to have related records.

Okay, I did:

=ConcatRelated("CompanyNameTitle","qryCrossRef","CompanyID = " & [CompanyID])

Is there a way to put 1, 2, 3... in front--meaning: 1) ABC123; 2) ABCxyz; and so on?

 

[vbaInet]

See attached and notice the query and the control source used.

 

[vbaInet]

Okay, I did:

=ConcatRelated("CompanyNameTitle","qryCrossRef","CompanyID = " & [CompanyID])

Is there a way to put 1, 2, 3... in front--meaning: 1) ABC123; 2) ABCxyz; and so on?

You would have to extract the number part from the string for you to be able to have a custom sort.

 

[lmcc007]

See attached and notice the query and the control source used.

Okay,

I built my query with:

tblCompanyName

CompanyNameTitle​

tblCompanyCompanyNameJunction

CompanyNameID
Type
DefaultForCompany​

Is that okay? Because I noticed you used all three tables.

 

[lmcc007]

You would have to extract the number part from the string for you to be able to have a custom sort.

No, that's okay--seems like a lot of work. But, thanks for replying!

 

[vbaInet]

I suppose it's whatever exists in the junction table that is needed. So yes you can just use the two but give that some more thought because you understand your setup more than myself.

 

[lmcc007]

I suppose it's whatever exists in the junction table that is needed. So yes you can just use the two but give that some more thought because you understand your setup more than myself.

No, not really. I am just testing--not sure it is correct.

 

[lmcc007]

See attached and notice the query and the control source used.

When I navigate to a record without any cross-references I get the following error:

Error 3075:  Syntax error (mission operator) in query expression 'CompanyID ='.

 

[lmcc007]

When I navigate to a record without any cross-references I get the following error:

Error 3075:  Syntax error (mission operator) in query expression 'CompanyID ='.

I think I fix the problem; I went to CompanyID field and enter this as the default:

=[Forms]![frmCompany]![CompanyID]

 

[vbaInet]

It's your junction table that's the key to the concatenation so those two tables are fine.

 

[lmcc007]

It's your junction table that's the key to the concatenation so those two tables are fine.

Thanks vbaInet for all you help! You have given me some good answers and links; it is very appreciative to get accurate information.

 

[vbaInet]

Happy to assist!