Concatenating multiple rows into single line in MS Access

Question

Possible Duplicate:
Combine rows in Access 2007
Access 2007 - Concatenate fields from one column in one table into a single, comma delmited value in another table

Currently I have a table structure that is somewhat like this:

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
Bob -------C1-------Inf--------7Per--------0.05------0-----ADC2
Bob -------C1-------Inf--------7Per--------0.05------2-----BAC2
Bob -------C1-------Inf--------7Per--------0.05------0-----RBE2
Bob -------C1-------Inf--------7Per--------0.05------8-----VBE2
Bob -------C1-------Inf--------7Per--------0.05------6-----AEC2
Bob -------C1-------Inf--------7Per--------0.05------0-----PBC2
Bob -------C2-------Com------8Per--------0.45------1-----XBC4
Bob -------C2-------Com------8Per--------0.45------0-----AEC2
Bob -------C2-------Com------8Per--------0.45------0-----PBC2
Bob -------C2-------Com------8Per--------0.45------3-----ADC2
Bob -------C2-------Com------8Per--------0.45------0-----ADC2
Bob -------C2-------Com------8Per--------0.45------0-----BAC2
Joe--------C1-------Inf---------7Per--------0.05------0-----PBC2
Joe--------C1-------Inf---------7Per--------0.05------0-----ZTM2
Joe--------C1-------Inf---------7Per--------0.05------2-----QYC2
Joe--------C1-------Inf---------7Per--------0.05------0-----FLC2
Joe--------C1-------Inf---------7Per--------0.05------1-----KSC2
Joe--------C1-------Inf---------7Per--------0.05------0-----JYC2

What i'm looking to do is have 1 line per "Name" and per "Cat", that will sum up all the "Err" (per "Name" and "Cat") and concatenate only the "BP" fields into a single line. Such as:

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
Bob -------C1-------Inf--------7Per--------0.05-----16-----BAC2, VBE2, AEC2
Bob -------C2------Com------8Per--------0.45------4------XBC4, ADC2
Joe--------C1-------Inf--------7Per--------0.05------3------QYC2, KSC2

There have been similar questions asked but I cannot seem to apply it as my knowledge of VBA scripting is beginner. Is there any way to do all of this via SQL? If VBA scripting is the only option (ie. creating a function), any help would be greatly appreciated. Thank You in advance.

Question part 2:
I created the function as per Allen Browne's guide. The module is saved as modConcatRelated. Now, i've tried to run this query (im not sure if this is the correct SQL to get the result that i'm looking for):

SELECT
    [Name],
    [Cat],
    [Desc],
    [Thresh],
    [Perc],
    sum([Err]),
    ConcatRelated("[BP]", "make_table_bp", "[Err] = " & [BP])
FROM make_table_bp
GROUP BY
    [Name],
    [Cat],
    [Desc],
    [Thresh],
    [Perc],
    [Err],
    [BP];

It said "Error 3061. Too few parameters. Expected 1." Also it said "Undefined Function ConcatRelated." I'm looking for guidance on how to create the correct SQL statement so that I can call the ConcatRelated function correctly and yield the result as depicted above. Thanks again.

Next question:
What if the table had a unique date field tagged on as the last column in the table. Something like this:

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP --- Date

Bob -------C1-------Inf--------7Per--------0.05------0-----ADC2--12/02/2011
Bob -------C1-------Inf--------7Per--------0.05------2-----BAC2--09/05/2011
Bob -------C1-------Inf--------7Per--------0.05------0-----RBE2--11/02/2011
Bob -------C1-------Inf--------7Per--------0.05------8-----VBE2--08/14/2012
Bob -------C1-------Inf--------7Per--------0.05------6-----AEC2--02/25/2009
Bob -------C1-------Inf--------7Per--------0.05------0-----PBC2--07/02/2011
Bob -------C2-------Com------8Per--------0.45------1-----XBC4--09/05/2011
Bob -------C2-------Com------8Per--------0.45------0-----AEC2--02/02/2010
Bob -------C2-------Com------8Per--------0.45------0-----PBC2--08/14/2012
Bob -------C2-------Com------8Per--------0.45------3-----ADC2--05/05/2001
Bob -------C2-------Com------8Per--------0.45------0-----ADC2--08/02/2010
Bob -------C2-------Com------8Per--------0.45------0-----BAC2--06/17/2010
Joe--------C1-------Inf---------7Per--------0.05------0-----PBC2--08/14/2012
Joe--------C1-------Inf---------7Per--------0.05------0-----ZTM2--09/05/2011
Joe--------C1-------Inf---------7Per--------0.05------2-----QYC2--05/17/2010
Joe--------C1-------Inf---------7Per--------0.05------0-----FLC2--3/19/2010
Joe--------C1-------Inf---------7Per--------0.05------1-----KSC2--09/05/2011
Joe--------C1-------Inf---------7Per--------0.05------0-----JYC2--08/14/2012

Let's say I wanted to build a query to say something like: show me all records still within this same format:

Name --- Cat --- Desc --- Thresh --- Perc --- Err --- BP
Bob -------C1-------Inf--------7Per--------0.05-----16-----BAC2, VBE2, AEC2
Bob -------C2------Com------8Per--------0.45------4------XBC4, ADC2
Joe--------C1-------Inf--------7Per--------0.05------3------QYC2, KSC2

But for a date range of 01/01/2009 to 09/31/2011

@HansUp could you help with this?

You need a VBA user-defined function for this. Allen Browne has provided one, along with a detailed example about how to use it. http://allenbrowne.com/func-concat.html — HansUp, Feb 09 '12 at 19:28
ive followed the steps of compiling the function but can you help me create my "select" statement based on what i'm looking for above? — JT2013, Feb 09 '12 at 19:52
after compiling it says "Undefined function 'ConcatRelated' in expression — JT2013, Feb 09 '12 at 20:19
Did you save the function code in a standard mode? (The code begins with `Public Function ConcatRelated` and ends with `End Function`) Not trying to insult you, but you're new to VBA, so I'm trying to guess where this went wrong. Are you attempting to use the ConcatRelated function in a query you're building with the query designer in an Access session? (A query can't use user-defined functions if the query is run from outside an Access session --- like from classic ASP, Dot.Net, PHP, etc) — HansUp, Feb 09 '12 at 20:59
@HansUp no offense taken! Once again I dont know much about VBA hence I'm reaching out. But back to the question, i had followed the steps as indicated in allen brown's document: 1) In Access, open the code window (e.g. press Ctrl+G.) 2) On the Insert menu, click Module. Access opens a new module window. 3) Paste in the function below. 4) On the Debug menu, click Compile, to ensure Access understands it. Then i clicked the save button. The module is named modConcatRelated. Now in my query I am trying to reference "ConcatRelated." Can you please point me in the right direction? — JT2013, Feb 10 '12 at 13:52
That all sounds correct to me. Switch your query from Design View to SQL View, copy the SQL text, and paste it into your question. Or open a new question if this one gets closed out. — HansUp, Feb 10 '12 at 14:07
You must resolve the "Undefined Function ConcatRelated" error. The steps you listed in your earlier comment look correct to me. So I don't know how to fix this one. — HansUp, Feb 10 '12 at 17:02
@HansUp my apologies, I thought positive comments helped ones reputation. My mistake — JT2013, Feb 17 '12 at 16:15

HansUp · Accepted Answer · 2012-02-13T16:30:03.107

I used a subquery for the GROUP BY which computes the Sum of Err for each group. Then I added the ConcatRelated function (from Allen Browne) with the fields returned by the subquery. This is the query and the output (based on your sample data in make_table_bp) from the query:

SELECT
    sub.[Name],
    sub.Cat,
    sub.[Desc],
    sub.Thresh,
    sub.Perc,
    sub.SumOfErr,
    ConcatRelated("BP",
        "make_table_bp",
        "[Err] > 0 AND [Name] = '" & sub.[Name]
        & "' AND Cat = '"
        & sub.Cat & "'",
        "BP")
        AS concat_BP
FROM
    (SELECT
        q.[Name],
        q.Cat,
        q.[Desc],
        q.Thresh,
        q.Perc,
        Sum(q.[Err]) AS SumOfErr
    FROM make_table_bp AS q
    GROUP BY
        q.[Name],
        q.Cat,
        q.[Desc],
        q.Thresh,
        q.Perc
    ) AS sub
ORDER BY
    sub.Name,
    sub.Cat;

The query outputs this result set:

Name Cat Desc Thresh Perc SumOfErr concat_BP
Bob  C1  Inf  7Per   0.05       16 AEC2, BAC2, VBE2
Bob  C2  Com  8Per   0.45        4 ADC2, XBC4
Joe  C1  Inf  7Per   0.05        3 KSC2, QYC2

Notice I enclosed Name, Desc, and Err with square brackets every place they were referenced in the query. All are reserved words (see Problem names and reserved words in Access). Choose different names for those fields if possible. If not, use the square brackets to avoid confusing the db engine.

But this will not work unless/until your copy of the ConcatRelated function is recognized by your data base engine. I don't understand why it's not; I followed the same steps you listed for storing the function code, and this works fine on my system.

Edit: I tested that query with my version of the table, which has [Err] as a numeric data type. Sounds like yours is text instead. In that case, I'll suggest you change yours to numeric, too. I don't see the benefit of storing numerical values as text instead of actual numbers.

However if you're stuck with [Err] as text, you can adapt the query to deal with it. Change this ...

"[Err] > 0 AND [Name] = '" & sub.[Name]

to this ...

"Val([Err]) > 0 AND [Name] = '" & sub.[Name]

That change prevented the "Data type mismatch in criteria expression" error when I tested with [Err] as text data type. However, I also changed this ...

Sum(q.[Err]) AS SumOfErr

to this ...

Sum(Val(q.[Err])) AS SumOfErr

AFAICT that second change is not strictly necessary. The db engine seems willing to accept numbers as text when you ask it to Sum() them. However I prefer to explicitly transform them to numerical values rather than depend on the db engine to make the right guess on my behalf. The db engine has enough other stuff to deal with, so I try to tell it exactly what I want.

Edit2: If you want only unique values concatenated, you can modify the ConcatRelated() function. Find this section of the code ...

'Build SQL string, and get the records.
strSql = "SELECT " & strField & " FROM " & strTable

and change it to this ...

'Build SQL string, and get the records.
strSql = "SELECT DISTINCT " & strField & " FROM " & strTable

when i run the above i get the following error: Error 3464: Data type mismatch in criteria expression....each field has a Text data type with the exception of the Perc field, which is a number — JT2013, Feb 10 '12 at 20:52
you are a genius! That worked like a charm! The only other question i have is: is there a way to have only disctinct records created in the concat_BP column? — JT2013, Feb 13 '12 at 16:20
thank you so much for your help...i've got the data exactly the way i need it! — JT2013, Feb 13 '12 at 17:04

Concatenating multiple rows into single line in MS Access

1 Answers1

Linked