从表中构建逗号分隔的列表 [英] Build comma-delimited list from table

问题描述

我的桌子看起来像这样：


森林居民

Black Josh

Black Joellen

Black Mary Jane

Brown Gertrude

Brown Josh

Brown Mary Jane

我已经看到一些用于SQL Server的UDF构建一个以逗号分隔的列表，但

我需要一个完全在Access中使用的函数来构建以下内容：


居民森林

Josh Black，Brown

Joellen Black

Mary Jane Black，Brown

Gertrude Brown


上表显示了每个人在

逗号分隔列表中访问过的森林。


有什么建议吗？

I have a table that looks like this:

Forest Residents
Black Josh
Black Joellen
Black Mary Jane
Brown Gertrude
Brown Josh
Brown Mary Jane
I''ve seen some UDFs for SQL Server to build a comma-delimited list, but
I need a function to use entirely in Access to build the following:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

Any suggestions?

推荐答案

每 ar **** @ yahoo。 com ：

居民森林
Josh Black，Brown
Joellen Black
Mary Jane Black，Brown
Gertrude布朗

上表显示了每个人在逗号分隔列表中访问过的森林。

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

如果我在做什么它，我打开按Person / Forest排序的源表，

只是遍历表，将PersonName | Tab | ForestName连接成一个

字符串变量 - 每次PersonName更改时将字符串刷新到磁盘上

（不要忘记在.EOF清空最后一个）

-

PeteCresswell

If I were doing it, I''d open up the source table sorted by Person/Forest and
just iterate through the table, concatenating PersonName|Tab|ForestName into a
string variable - and flushing the string to disk each time PersonName changes
(not forgetting to flush the last one at .EOF)
--
PeteCresswell

"（PeteCresswell）" < [e-mail]>写在

新闻：u2 ******************************** @ 4ax.com：

"(PeteCresswell)" <[e-mail]> wrote in
news:u2********************************@4ax.com:

每 ar****@yahoo.com ：

居民森林
Josh Black，Brown
Joellen Black
Mary Jane Black，Brown
Gertrude Brown

上表显示每个人的森林已经在逗号分隔的列表中访问了
。

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited
in a comma-delimited list.

如果我这样做，我会打开源表，按人物/森林和只需遍历表格，
将PersonName | Tab | ForestName连接成一个字符串变量
- 每次PersonName更改时将字符串刷新到磁盘上（不要忘记在.EOF中刷新最后一个） ）

If I were doing it, I''d open up the source table sorted by
Person/Forest and just iterate through the table,
concatenating PersonName|Tab|ForestName into a string variable
- and flushing the string to disk each time PersonName changes
(not forgetting to flush the last one at .EOF)

这就是Dev Ashish编写fConcatChild函数的确切方法
http://www.mvps.org/access/modules/mdl0004.htm

为什么重新发明轮子？


-

Bob Quintal


PA是我改变了我的电子邮件地址。

That''s exactly how Dev Ashish wrote the fConcatChild function
http://www.mvps.org/access/modules/mdl0004.htm

Why reinvent the wheel?

--
Bob Quintal

PA is y I''ve altered my email address.

ar **** @ yahoo.com 写道：

我的桌子看起来像这样：

森林居民
Black Josh <黑色Joellen
Black Mary Jane
Brown Gertrude
Brown Josh
Brown Mary Jane

我见过一些针对SQL Server的UDF建立一个以逗号分隔的列表，但是
我需要一个完全在Access中使用的功能来构建以下内容：

居民森林
Josh Black，Brown
Joellen Black
Mary Jane Black，Brown
Gertrude Brown

上表显示了每个人在逗号分隔列表中访问过的森林。

有什么建议吗？

I have a table that looks like this:

Forest Residents
Black Josh
Black Joellen
Black Mary Jane
Brown Gertrude
Brown Josh
Brown Mary Jane
I''ve seen some UDFs for SQL Server to build a comma-delimited list, but
I need a function to use entirely in Access to build the following:

Residents Forests
Josh Black, Brown
Joellen Black
Mary Jane Black, Brown
Gertrude Brown

The table above indicates the forests each person has visited in a
comma-delimited list.

Any suggestions?

首先，我假设他们是一个 ; ResidentID"在你的许多表格中的字段
上面的
它看起来如下：


ResidentID Forest Residents

1 Black Josh

2 Black Joellen

3 Black Mary Jane

4 Brown Gertrude

1 Brown Josh

3布朗玛丽珍


如果是这样的话，请将以下功能（Allen的修改版本

Browne）放入模块中：


函数ConcatDetail（Num As Long）作为Variant

Dim MyDB作为数据库

Dim rst作为记录集

Dim strOut As String

Dim strSql As String

Dim lngLen As Long


Const strcSep ="，"


设置MyDB = CurrentDb（）

''将Table1更改为表的名称

strSql =" SELECT Forest FROM Table1 WHERE ResidentID =" &安培; Num& " ;;"

设置rst = MyDB.OpenRecordset（strSql）

rst

Do While Not .EOF

strOut = strOut& ！森林& strcSep

.MoveNext

循环

结束

rst.Close


lngLen = Len（strOut） - Len（strcSep）

如果lngLen> 0然后

ConcatDetail =左（strOut，lngLen）

否则

ConcatDetail = Null

结束如果


Set rst = Nothing

设置MyDB = Nothing

结束功能

接下来，创建查询表格（上面显示的第一个表格和本例中名为Table1的
）。添加居民现场并创建

旁边的另一个字段，例如


森林：ConcatDetail（[ResidentID]）


运行此查询，你应该看看你需要什么。


osmethod

Firstly, I''m assuming their is a "ResidentID" field in your many table
above and it looks as follows:

ResidentID Forest Residents
1 Black Josh
2 Black Joellen
3 Black Mary Jane
4 Brown Gertrude
1 Brown Josh
3 Brown Mary Jane

If so, put the following function (a modified version of one by Allen
Browne) into a module:

Function ConcatDetail(Num As Long) As Variant
Dim MyDB As Database
Dim rst As Recordset
Dim strOut As String
Dim strSql As String
Dim lngLen As Long

Const strcSep = ","

Set MyDB = CurrentDb()

''Change Table1 to your table''s name
strSql = "SELECT Forest FROM Table1 WHERE ResidentID = " & Num & ";"
Set rst = MyDB.OpenRecordset(strSql)
With rst
Do While Not .EOF
strOut = strOut & !Forest & strcSep
.MoveNext
Loop
End With
rst.Close

lngLen = Len(strOut) - Len(strcSep)
If lngLen > 0 Then
ConcatDetail = Left(strOut, lngLen)
Else
ConcatDetail = Null
End If

Set rst = Nothing
Set MyDB = Nothing
End Function
Next, Create a query on the table (Your 1st table shown above and
called Table1 in this example). Add the "Resident" field and create
another field beside this e.g.

Forests: ConcatDetail([ResidentID])

Run this query and you should see what you require.

osmethod

这篇关于从表中构建逗号分隔的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！