There is no custom function in the Access SQL syntax, nor can you flexibly process query statements like a variable, a cursor, a subquery, etc. in SQL Server. However, in the Access environment, through the process of the module, the powerful VB language can be utilized to generate a query statement of complex demand. Below is a reply in the Access section today, in this example, simply describes the powerful features contained in VB. User questions: ----------------------------------------------- --------------------------------- I in Access is as follows: COMNAME NAME SEX 1 company Xiao Wang male 1 Company Xiao Li 2 Company Xiao Zhang
I want to be with the company's content as: COMNAME NAME SEX 1 company Xiao Wang, Xiao Li male, female 2 company Xiao Zhang male
I should be operational in Access? Is there any good way to deal with? ? -------------------------------------------------- ------------------------------answer:------------------ -------------------------------------------------- ------------ Create a custom function in the ACCESS module:
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" & GroupField & "= '" & GroupValue & "'") If rs.RecordCount> 0 Then do while not rs.eof resultstr = resultstr & "," & rs.fields (0) .value rs.movenext loop endiffstr = mid (resultstr, 2) Combstr = Resultstr End Function
Establish a query:
Select T.Comname, Combstr ("T", "Name", "Comname", T.Comname) As Combname, Combstr ("T", "SES", "COMNAME", T.COMNAME) AS CombsexFrom TGROUP BY T. COMNAME
