Microsoft Access 在表中压缩多行 [英] Microsoft Access condense multiple lines in a table
问题描述
我在 MS Access 2007 中有一个问题,希望有人能解答.我有一个长而简单的表格,其中包含客户姓名和交货日期.我想通过在一个新字段ALLDays"中列出名称和所有天数来总结此表,同时仍保留所有数据.
I have a question in MS Access 2007 and I hope someone has the answer. I have a long but simple table containing customer names and the days of the week that deliveries are made. I would like to summarize this table by listing the name and all the days into one new field "ALLDays" while still preserving all the data.
源表如下所示:
Name Day
CustomerA Monday
CustomerA Thursday
CustomerB Tuesday
CustomerB Friday
CustomerC Wednesday
CustomerC Saturday
我想要一个返回如下结果的查询:
I would like to have a query which returns results like this:
Name ALLDays
CustomerA Monday, Thursday
CustomerB Tuesday, Friday
CustomerC Wednesday, Saturday
谢谢.
推荐答案
通常,您必须编写一个函数来创建串联列表.这是我用过的:.
Typically you have to write a function that will allow you to create a concatenated list. Here's what I've used:.
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 语句.
Remou's version has the added feature that you can pass an array of values instead of a SQL statement.
示例查询可能如下所示:
Sample query might look like:
SELECT SourceTable.Name
, GetList("Select Day From SourceTable As T1 Where T1.Name = """ & [SourceTable].[Name] & """","",", ") AS Expr1
FROM SourceTable
GROUP BY SourceTable.Name;
这篇关于Microsoft Access 在表中压缩多行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!