我在MS Access 2007中有一个问题,我希望有人有答案.我有一个长而简单的表,其中包含客户名称和一周内交付的日期.我想通过将名称和所有日子列入一个新字段“ALLDays”,同时仍保留所有数据来总结此表.
源表看起来像这样：
Name Day CustomerA Monday CustomerA Thursday CustomerB Tuesday CustomerB Friday CustomerC Wednesday CustomerC Saturday
我想要一个返回结果的查询：
Name ALLDays CustomerA Monday,Thursday CustomerB Tuesday,Friday CustomerC Wednesday,Saturday
谢谢.
通常你必须编写一个函数,让你创建一个连接的列表.这是我使用的：
Public Function GetList(sql As String _,Optional ColumnDelimeter As String = "," _,Optional RowDelimeter As String = vbCrLf) As String 'PURPOSE: to return a combined string from the passed query 'ARGS: ' 1. sql is a valid Select statement ' 2. ColumnDelimiter is the character(s) that separate each column ' 3. RowDelimiter is the character(s) that separate each row 'RETURN VAL: Concatenated list 'DESIGN NOTES: 'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key) Const PROCNAME = "GetList" Const adClipString = 2 Dim oConn As ADODB.Connection Dim oRS As ADODB.Recordset Dim sResult As String On Error GoTo ProcErr Set oConn = CurrentProject.Connection Set oRS = oConn.Execute(sql) sResult = oRS.GetString(adClipString,-1,ColumnDelimeter,RowDelimeter) If Right(sResult,Len(RowDelimeter)) = RowDelimeter Then sResult = Mid$(sResult,1,Len(sResult) - Len(RowDelimeter)) End If GetList = sResult oRS.Close oConn.Close CleanUp: Set oRS = Nothing Set oConn = Nothing Exit Function ProcErr: ' insert error handler Resume CleanUp End Function
Remou的版本具有添加的功能,您可以传递一个值数组,而不是sql语句.
示例查询可能如下所示：
SELECT SourceTable.Name,GetList("Select Day From SourceTable As T1 Where T1.Name = """ & [SourceTable].[Name] & """","",",") AS Expr1
FROM SourceTable
GROUP BY SourceTable.Name;
