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;
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
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;
How to concatenate multiple rows in Access involving a Link?
You can use my DJoin function for this:
SELECT
tblComponents.compname,
DJoin(
"errname",
"SELECT compID, errname
FROM linkComponentsErrors
INNER JOIN tblErrors ON linkComponentsErrors.errID = tblErrors.errID",
"compID = " & [tblComponents].[compID] & "",
", ") AS errnames
FROM
tblComponents
INNER JOIN
linkComponentsErrors ON tblComponents.compID = linkComponentsErrors.compID
GROUP BY
tblComponents.compname,
DJoin(
"errname",
"SELECT compID, errname
FROM linkComponentsErrors
INNER JOIN tblErrors ON linkComponentsErrors.errID = tblErrors.errID",
"compID = " & [tblComponents].[compID] & "",
", "),
tblComponents.compID
ORDER BY
tblComponents.compID;
Output:
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.
How to concatenate text from multiple rows into a single text string in SQL Server
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)') [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2
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
Odata Case In-Sensitive Filtering in Web API
Sql: When It Comes to Not in and Not Equal To, Which Is More Efficient and Why
Oledb Case When in Select Query
Any Detailed and Specific Reasons for Why Mongodb Is Much Faster Than SQL Dbs
Execute SQL Script from Command Line
How to Make Shiny Reactivity Work with SQL Database
Finding the Data Types of a SQL Temporary Table
How to Concatenate Text from Multiple Rows into a Single Text String in Oracle Server
Big Query - Create a Table/View from a Temp Table
Rails: How to Find_By a Field Containing a Certain String
General Rules for Simplifying SQL Statements
SQL Queries on String Columns - Sorting According to Language
Return Boolean Value on SQL Select Statement
For Each Quarter Between Two Dates, Add Rows Quarter by Quarter in SQL Server