A method for querying a concatenation string in access for a multiple-row field value in a group

access| Group

There is no custom function in Access's SQL syntax, and you cannot flexibly handle query statements in the same way as SQL Server with variables, cursors, subqueries, and so on.

However, in the access environment, through the process in the module, the powerful VB language can be used to generate complex requirements of the query statement.

Here is a reply I have in Access today, to this example, a simple explanation of access relies on VB's powerful features.

User questions:
--------------------------------------------------------------------------------
My table in Access is as follows:
Comname name Sex
1 company Xiao Wang Man
1 company Xiao Li
2 company Xiao Zhang Man

I would like to combine the contents of the company with the following:
Comname name Sex
1 company Xiao Wang, Xiao Li man, female
2 company Xiao Zhang Man

What if I can operate in Access? What is the best way to deal with it?
--------------------------------------------------------------------------------

Answer:
----------------------------------------------------------------------------------
Establish a custom function in the module of access:

Public Function Combstr (tablename As String, FieldName as String, GroupField as String, Groupvalue as String) as String

Dim ResultStr as String
Dim RS as Recordset
Set rs = currentdb.openrecordset ("Select" & FieldName & "from" & TableName & "where" & Groupfiel D & "= '" & Groupvalue & "")
If Rs. RecordCount > 0 Then
Do but not Rs. Eof
ResultStr = ResultStr & "," & Rs. Fields (0). Value
Rs. MoveNext
Loop
End If
If resultstr <> "" Then resultstr = Mid (ResultStr, 2)
Combstr = ResultStr

End Function



Create a query:

SELECT T.comname, Combstr ("T", "Name", "Comname", T.comname) as Combname, Combstr ("T", "ses", "comname", T.comname) as Combsex
From T
GROUP by T.comname

----------------------------------------------------------------------------------------------------