Concatenating multiple records into one text field

[Tomas C]

Howdy Folks,

I've done some searching, but I cannot figure out how to concatenate
multiple records into one field. I have a query that is pulling out some
chemistry data for me. I'd like to use it as the source for concatenation.

Query Name: qsel_basic_result_sve_other_det

sys_sample_code [string]: Unique ID for the query.
concat_result [string]: The field containing the text values I want to
concatenate. This field looks like "Benzene = 4.5" and are essentially
infinitely variable. That is, there's not a list of valid values in this
field.

In many cases, I have multiple records for a given location, although
sometimes there is only one. I'd like to have something that looks like:

MW-1 Benzene = 4.5, TCE = 3, PCE = 400
MW-2 TCE = 50
MW-3 Lead = 22; Benzene = 500

I checked out:

http://www.mvps.org/access/modules/mdl0004.htm

along with a few other pages and I just can't make sense out of what is
going on. I need to customize the code to work with my query, but I'm
not a strong enough programmer to understand it. Can anyone offer some
advice. Any help is sincerely appreciated.

Tom

 

[Duane Hookom]

I prefer the generic concatenate function with sample usage found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[Tomas C]

Howdy Duane,

Thank you for pointing me to your page with the database example. I'm
struggling a bit with your code. Will it work if my primary key is not a
number?

I think that your query "Example of First Names in Family" is what I am
looking for. I created a table that contains only the primary keys for
my groundwater samples to more closely replicate your structure. My
query to stitch everything together looks like:

SELECT tbl_unique_sys_sample_codes.sys_sample_code, Concatenate("SELECT
concat_result FROM tbl_basic_result_sve_other_det WHERE sys_sample_code
=" & [sys_sample_code]) AS SampCodes
FROM tbl_unique_sys_sample_codes;

However, when I run it, I get the error: "No value given for one or more
required parameters." when I debug, it takes me to this line in your module:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas? Thanks again.

Tom

 

[Tomas C]

Apparently it only works if I create integer sample keys. I was able to
get the query working if I manually created autonumber keys and ran the
appropriate update query. If there's a tweak to your code that would
enable the use of text keys, I would be very interested. Thanks for
getting me most of the way there, however.

Tom

Howdy Duane,

Thank you for pointing me to your page with the database example. I'm
struggling a bit with your code. Will it work if my primary key is not a
number?

I think that your query "Example of First Names in Family" is what I am
looking for. I created a table that contains only the primary keys for
my groundwater samples to more closely replicate your structure. My
query to stitch everything together looks like:

SELECT tbl_unique_sys_sample_codes.sys_sample_code, Concatenate("SELECT
concat_result FROM tbl_basic_result_sve_other_det WHERE sys_sample_code
=" & [sys_sample_code]) AS SampCodes
FROM tbl_unique_sys_sample_codes;

However, when I run it, I get the error: "No value given for one or more
required parameters." when I debug, it takes me to this line in your
module:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas? Thanks again.

Tom

I prefer the generic concatenate function with sample usage found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

 

[John Spencer]

Try the following.

SELECT tbl_unique_sys_sample_codes.sys_sample_code,
Concatenate("SELECT concat_result FROM tbl_basic_result_sve_other_det
WHERE sys_sample_code =""" & [sys_sample_code] & """") AS SampCodes
FROM tbl_unique_sys_sample_codes;


Also, if you are not using an Access Project then you need to comment
out some lines of code and comment in other lines of code in the
concatenate function. Duane's code specifies which lines to change.

An apostrophe at the beginning of a line comments OUT the code.
Removing the apostrophe makes that line of code active.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Howdy Duane,

Thank you for pointing me to your page with the database example. I'm
struggling a bit with your code. Will it work if my primary key is not a
number?

I think that your query "Example of First Names in Family" is what I am
looking for. I created a table that contains only the primary keys for
my groundwater samples to more closely replicate your structure. My
query to stitch everything together looks like:

SELECT tbl_unique_sys_sample_codes.sys_sample_code, Concatenate("SELECT
concat_result FROM tbl_basic_result_sve_other_det WHERE sys_sample_code
=" & [sys_sample_code]) AS SampCodes
FROM tbl_unique_sys_sample_codes;

However, when I run it, I get the error: "No value given for one or more
required parameters." when I debug, it takes me to this line in your
module:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Any ideas? Thanks again.

Tom

I prefer the generic concatenate function with sample usage found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.