为了账号安全,请及时绑定邮箱和手机立即绑定

Microsoft Access压缩表中的多行

/ 猿问

Microsoft Access压缩表中的多行

德玛西亚99 2019-09-03 19:48:52

我在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  

谢谢。


查看完整描述

3 回答

?
PIPIONE

通常,您必须编写一个允许您创建连锁列表的函数。这是我用过的:


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;


查看完整回答
反对 回复 2019-09-03
?
幕布斯6054654

这是一个不需要VBA的简单解决方案。它使用更新查询将值连接到字段上。


我会用我正在使用的例子来展示它。


我有一个表“emails_by_team”,它有两个字段“team_id”和“email_formatted”。我想要的是在一个字符串中收集给定团队的所有电子邮件。


1)我创建了一个表“team_more_info”,它有两个字段:“team_id”和“team_emails”


2)使用“emails_by_team”中的所有“team_id”填充“team_more_info”


3)创建一个更新查询,将“emails_by_team”设置为NULL 

查询名称:team_email_collection_clear


UPDATE team_more_info 

SET team_more_info.team_emails = Null;

4)这是诀窍:创建更新查询

查询名称:team_email_collection_update


UPDATE team_more_info INNER JOIN emails_by_team 

  ON team_more_info.team_id = emails_by_team.team_id 

SET team_more_info.team_emails = 

    IIf(IsNull([team_emails]),[email_formatted],[team_emails] & "; " & [email_formatted]);

5)保持信息最新创建一个宏,在需要时运行两个查询


第一:team_email_collection_clear


第二:team_email_collection_update


QED


查看完整回答
反对 回复 2019-09-03
?
慕仰8121524

由于这只是一小部分选项,另一种没有VBA的方法是设置一系列IIF语句并连接结果。


SELECT name, 

   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 

   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 

   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 

   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &

   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &

   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &

   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ") AS AllDays

FROM Table1

GROUP BY name

如果你是一个完美主义者,你甚至可以摆脱这样的最后一个逗号


SELECT name, 

LEFT(

   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 

   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 

   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 

   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &

   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &

   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &

   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, "),

LEN(

   IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday, ") & 

   IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday, ") & 

   IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday, ") & 

   IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday, ") &

   IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday, ") &

   IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday, ") &

   IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday, ")

) - 2

)

AS AllDays

FROM Table1

GROUP BY name

您还可以考虑将它们保存在单独的列中,因为如果从另一个列访问此查询,这可能会更有用。例如,通过这种方式查找只有星期二的实例会更容易。就像是:


SELECT name, 

IIF(SUM(IIF(day = "Monday",1,0)) >0, "Monday") AS Monday,  

IIF(SUM(IIF(day = "Tuesday",1,0)) >0, "Tuesday") AS Tuesday,

IIF(SUM(IIF(day = "Wednesday",1,0)) >0, "Wednesday") AS Wednesday,

IIF(SUM(IIF(day = "Thursday",1,0)) >0, "Thursday") AS Thursday,

IIF(SUM(IIF(day = "Friday",1,0)) >0, "Friday") AS Friday,

IIF(SUM(IIF(day = "Saturday",1,0)) >0, "Saturday") AS Saturday,

IIF(SUM(IIF(day = "Sunday",1,0)) >0, "Sunday") AS Sunday

FROM Table1

GROUP BY name


查看完整回答
反对 回复 2019-09-03

添加回答

回复

举报

0/150
提交
取消
意见反馈 帮助中心 APP下载
官方微信