A method for querying a concatenation string in access for a multiple-row field value in a group
Source: Internet
Author: User
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
The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion;
products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the
content of the page makes you feel confusing, please write us an email, we will handle the problem
within 5 days after receiving your email.
If you find any instances of plagiarism from the community, please send an email to:
info-contact@alibabacloud.com
and provide relevant evidence. A staff member will contact you within 5 working days.