Concatenating multiple rows into single line in MS Access [duplicate]

by

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

More Related Contents:

  1. ConcatRelated() in Access, using two keys
  2. ConcatRelated function in a query
  3. How do i add percentage an Access query?
  4. How does one decompile and recompile a database application?
  5. How do you use version control with Access development?
  6. What are the rules governing usage of parenthesis in VBA function calls?
  7. Autonumber value of last inserted row – MS Access / VBA
  8. Microsoft Access condense multiple lines in a table
  9. How to get random record from MS Access database
  10. Code to loop through all records in MS Access
  11. Ms Access Query: Concatenating Rows through a query
  12. Syntax error in insert into statement – what’s the error?
  13. create form to add records in multiple tables
  14. Difference between Microsoft.Jet.OleDb and Microsoft.Ace.OleDb
  15. Why is my query not updateable?
  16. Microsoft Access DBEngine.OpenDatabase() breaks for Network-Paths – Error 3050
  17. Is it possible to create a recursive query in Access?
  18. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  19. What is the equivalent of Select Case in Access SQL?
  20. How do I access the selected rows in Access?
  21. Access 2007 – Left Join to a query returns #Error instead of Null
  22. Pivot Query in MS Access
  23. insert query with sequential primary key
  24. How do I execute multiple SQL Statements in Access’ Query Editor?
  25. how to show query results in a datasheet in the same form?
  26. Selecting 2 tables from 2 different databases (ACCESS)
  27. connected combo boxes in continuous form – the second combo doesn’t show its value if dependent on first combo how could I fix that
  28. MS Access prepared statements
  29. Linked table ms access 2010 change connection string
  30. Create sequential ID value based on the year that a record is added

Leave a Comment