concatenated list

[Guest]

Hello again,

I went to http://www.mvps.org/access/modules/mdl0004.htm to learn about how
to concatenate lists. The problem is that I have no idea how to add it into
the query string. Where do I save it in Access? How and where do I pull it
up in a query? And does this code work if I want to concatenate an entire
list rather than one record?

I am not an advanced programmer and don't really understand VB. I just need
a little more handholding.

Any help would be appreciated.

 

[Arvin Meyer [MVP]]

The concatenation code below, works on a single field, and concatenates a
list of values in that field. I would be used, for instance to create a list
of last names that were workers on a project. You can use that code on a
query as a recordset, and output the values to a control, but you couldn't
use it in a query since it can't run for all rows in a single row.

To concatenate a single record in a query, you'd use something like:

Expr1: [Field1] & ", " & [Field2] & ", " & [Field3] & ", " & [Field4]

to do the same on a form, try somethin like:

= [Text1] & ", " & [Text2] & ", " & [Text3] & ", " & [Text4]
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks Arvin,

I should have provided an example, because I think the solution is a bit
more difficult, but hopefully I'm wrong. This is what I'm trying to do:

Record Number, Household, Individual
238, Smith Family, John Smith
238, Smith Family, Jane Smith
240, Bush Family, Joel Bush
240, Bush Family, Jared Bush

To…

Record Number, Household, Individual 1, Individual 2
238, Smith Family, John Smith, Jane Smith
240, Bush Family, Joel Bush, Jared Bush

Any guesses?

The concatenation code below, works on a single field, and concatenates a
list of values in that field. I would be used, for instance to create a list
of last names that were workers on a project. You can use that code on a
query as a recordset, and output the values to a control, but you couldn't
use it in a query since it can't run for all rows in a single row.

To concatenate a single record in a query, you'd use something like:

Expr1: [Field1] & ", " & [Field2] & ", " & [Field3] & ", " & [Field4]

to do the same on a form, try somethin like:

= [Text1] & ", " & [Text2] & ", " & [Text3] & ", " & [Text4]
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello again,

I went to http://www.mvps.org/access/modules/mdl0004.htm to learn about
how
to concatenate lists. The problem is that I have no idea how to add it
into
the query string. Where do I save it in Access? How and where do I pull
it
up in a query? And does this code work if I want to concatenate an entire
list rather than one record?

I am not an advanced programmer and don't really understand VB. I just
need
a little more handholding.

Any help would be appreciated.

 

[Arvin Meyer [MVP]]

First, I'm not sure I understand how you are using Record Number. Two
different records cannot have the same number. Is your Record Number
actually a HouseholdID?

Assuming that it is, you use the concatenation code on the "Individual"
field.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Thanks Arvin,

I should have provided an example, because I think the solution is a bit
more difficult, but hopefully I'm wrong. This is what I'm trying to do:

Record Number, Household, Individual
238, Smith Family, John Smith
238, Smith Family, Jane Smith
240, Bush Family, Joel Bush
240, Bush Family, Jared Bush

To.

Record Number, Household, Individual 1, Individual 2
238, Smith Family, John Smith, Jane Smith
240, Bush Family, Joel Bush, Jared Bush

Any guesses?

The concatenation code below, works on a single field, and concatenates a
list of values in that field. I would be used, for instance to create a
list
of last names that were workers on a project. You can use that code on a
query as a recordset, and output the values to a control, but you
couldn't
use it in a query since it can't run for all rows in a single row.

To concatenate a single record in a query, you'd use something like:

Expr1: [Field1] & ", " & [Field2] & ", " & [Field3] & ", " & [Field4]

to do the same on a form, try somethin like:

= [Text1] & ", " & [Text2] & ", " & [Text3] & ", " & [Text4]
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello again,

I went to http://www.mvps.org/access/modules/mdl0004.htm to learn about
how
to concatenate lists. The problem is that I have no idea how to add it
into
the query string. Where do I save it in Access? How and where do I
pull
it
up in a query? And does this code work if I want to concatenate an
entire
list rather than one record?

I am not an advanced programmer and don't really understand VB. I just
need
a little more handholding.

Any help would be appreciated.