Ms Access Query: Concatenating Rows Through a Query

Ms Access Query: Concatenating Rows through a query

You need a function to do the concatenation.

Microsoft Access condense multiple lines in a table

Example using your data:

Select T.ColumnA
  , GetList("Select ColumnB From Table1 As T1 Where T1.ColumnA = " & [T].[ColumnA],"",", ") AS ColumnBItems
From Table1 AS T
Group By T.ColumnA;

Concatenate Rows in Microsoft Access

Create a module and add the following code:

Function liststuff(company)

    Dim curr As Database
    Dim rs As Recordset
    Dim SQLCmd As String
    Dim productList As String

    Set curr = CurrentDb()

    SQLCmd = "SELECT Product FROM table1 WHERE [Company Name] = """ & company & """"

    Set rs = curr.OpenRecordset(SQLCmd)

    If Not rs.EOF Then
        rs.MoveFirst
    End If

    Do While Not rs.EOF
        productList = productList & rs(0) & ", "
        rs.MoveNext
    Loop

    liststuff = productList

End Function

You may need to change the database values to your actual table name and field names.

In your query use:

SELECT Table1.[Company Name], Table1.Address, Liststuff([Company Name]) AS [List of Products]
FROM Table1
GROUP BY Table1.[Company Name], Table1.Address;

Concatenating multiple rows into single line in MS Access

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

Concatenating multiple rows, with multiple values in it, into single line in MS Access

Build a query that expands the multi-value field elements to individual records.

Query1

SELECT SW_Requirements.Title, SW_Requirements.SW_ReqID, SW_Requirements.RootReq.Value 
FROM SW_Requirements;

Then use that query as source for ConcatRelated() function.

SELECT Contract_Requirements.*, 
ConcatRelated("SW_ReqID","Query1","[SW_Requirements.RootReq.Value]='" & [CR_ReqID] & "'") AS WhereUsed
FROM Contract_Requirements;

Advise not to use spaces nor punctuation/special characters in naming convention.

MS Access 2013 Concatenate Rows query

Consider the following query with subquery and derived table nested in an aggregate query. Inner query counts number of ItemValues per ItemID and then outer query conditionally aggregates by [Name] and Description using calculated count to output a concatenation string of Item's Values.

Unfortunately, you will need to explicitly add Max(IIF(...) query statements for each value to be concatenated as below only uses three from your example. See ellipsis.

Please note: this solution assumes you have a primary, autonumber ID field in [ItemValues] table:

SELECT Items.Name, Items.Description,
       Max(IIF(ItemCount=1, t1.Value, NULL)) & ';' &  
       Max(IIF(ItemCount=2, t1.Value, NULL)) & ';' & 
       Max(IIF(ItemCount=3, t1.Value, NULL)) 
       ... As ItemValues       
FROM(
     SELECT Items.Name, Items.Description, t1.Value,
            (SELECT Count(*) FROM ItemValues t2 
             WHERE t1.ItemID = t2.ItemID AND t1.ID >=t2.ID) As ItemCount
     FROM Items INNER JOIN ItemValues t1 ON Items.ID = t1.ItemID
) As derivedTable
GROUP BY Items.Name, Items.Description;

OUTPUT

Name        Description            ItemValues
Testitem    This is a test item    itemval1;property2val;1234foo

By the way, your Items and ItemValues table seems to resemble the Entity-Attribute Value (EAV) model where multiple data items and types are stored in a single column. Though it makes for efficient storage at an extreme type of normalization, this model is generally not recommended in database design. See SO posts on the subject: here and here. Consider a revised table schema to avoid complex queries down the road.

Combine values from related rows into a single concatenated string value

This is easy using Allen Browne's ConcatRelated() function. Copy the function from that web page and paste it into an Access standard code module.

Then this query will return what you asked for.

SELECT
    i.N_ID,
    i.F_Name,
    i.L_Name,
    ConcatRelated(
        "Course_ID",
        "tbl_Courses",
        "N_ID = '" & [N_ID] & "'"
        ) AS Course_IDs
FROM tbl_Instructors AS i;

Consider changing the data type of N_ID from text to numeric in both tables. If you do that, you don't need the single quotes in the third argument to that ConcatRelated() expression.

"N_ID = " & [N_ID]

And whenever you need N_ID displayed with leading zeros, use a Format() expression.

Format(N_ID, "000")

Concatenate records and GROUP BY in Access

There is no Group_Concat in Access :/. Probably there is no solution that excludes VBA.

Here is one possible: Concatenating Rows through a query

---

Related Topics