连接Microsoft Access中的行 [英] Concatenate Rows in Microsoft Access

问题描述

基本思想是我在Access 2007中有一个这种形式的表:

The basic idea is that I have a table in this form in Access 2007:

Company Name |     Address     | Product
Company A      123 Fakestreet    Phone
Company A      123 Fakestreet    Computer
Company A      123 Fakestreet    Car
Company B      456 Fakestreet    Football
Company B      456 Fakestreet    Basketball
Company B      456 Fakestreet    Golf Ball

我希望它采用这种形式:

And I want it to be in this form:

Company Name |     Address     | List of Products
Company A      123 Fakestreet    Phone, Computer, Car
Company B      456 Fakestreet    Football, Basketball, Golf Ball

我尝试使用艾伦·布朗的ConcatRelated函数( http://allenbrowne.com/func-concat.html )，但起初它不起作用，因为我忘记了启用VBA内容.我这样做了，现在Access一直保持冻结并且没有响应.我在下面使用查询(表的名称为Addresses).我忽略了地址一栏，只是想看看是否可以使用它，然后再放回去.:

I tried using Allen Browne's ConcatRelated function (http://allenbrowne.com/func-concat.html), and at first it wouldn't work because I forgot to enable VBA content. I did that, and now Access just keeps freezing and not responding. I used the query below (the name of the table is Addresses). I left out the column Address just to see if I could get it to work, then I was going to put it back in.:

SELECT Company_Name, ConcatRelated("Product","Addresses")
FROM Addresses;

有人看到它有什么问题吗?我唯一能想到的是表的大小(约290,000行)可能会使其变得太慢，但是我在表上运行了不同的查询而没有问题.

Does anyone see anything wrong with it? The only thing I can think of is that the size of my table (~290,000 rows) could be making it too slow, but I've run different queries on the table without an issue.

推荐答案

创建一个模块并添加以下代码:

Create a module and add the following code:

Function liststuff(company)

    Dim curr As Database
    Dim rs As Recordset
    Dim SQLCmd As String
    Dim productList As String

    Set curr = CurrentDb()

    SQLCmd = "SELECT Product FROM table1 WHERE [Company Name] = """ & company & """"

    Set rs = curr.OpenRecordset(SQLCmd)

    If Not rs.EOF Then
        rs.MoveFirst
    End If

    Do While Not rs.EOF
        productList = productList & rs(0) & ", "
        rs.MoveNext
    Loop

    liststuff = productList

End Function

您可能需要将数据库值更改为实际的表名和字段名.

You may need to change the database values to your actual table name and field names.

在查询中使用:

SELECT Table1.[Company Name], Table1.Address, Liststuff([Company Name]) AS [List of Products]
FROM Table1
GROUP BY Table1.[Company Name], Table1.Address;

这篇关于连接Microsoft Access中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！