如何在MS-Access中显示分成一行的3条记录?

I want the data in table1 to display as in table in MS access 2000

我希望table1中的数据显示在access 2000的表中。

| contact_id | name                           | contact_type | 
| 297        | Primary Properties Corporation | Supplier     |
| 297        | Primary Properties Corporation | Prospect     |
| 297        | Primary Properties Corporation | Customer     |
| 298        | San Miguel Corporation         | Prospect     |
| 301        | Sulpicio Lines                 | Supplier     |

I would like it to return:

我希望它回来:

| contact_id | name                           | contact_type    
|  297       | Primary Properties Corporation | Supplier, Prospect, Customer |
|  298       | San Miguel Corporation         | Prospect                     |
|  301       | Sulpicio Lines                 | Supplier                     |

I got some ways like using group concat, xml_path in sql, but it doesn't work in ms access.

我得到了一些方法，比如在sql中使用group concat, xml_path，但它在ms access中不起作用。

Please guide me in this.

请指导我做这件事。

1 个解决方案

#1

Here's one way:

这里有一个方法:

Open the Visual Basic Editor... Tools --> Macro --> Visual Basic Editor (or AltF11)

打开Visual Basic编辑器…工具——>宏——> Visual Basic编辑器(或AltF11)

Insert a module and paste in this UDF:

在这个UDF中插入一个模块和粘贴:

'Concat returns a comma-seperated list of items
Public Function Concat (CategoryCol As String, _
                        ItemCol     As String) As String
    Static LastCategory As String
    Static ItemList     As String

    If CategoryCol      = LastCategory Then
        ItemList        = ItemList & ", " & ItemCol
    Else
        LastCategory    = CategoryCol
        ItemList        = ItemCol
    End If
    Concat = ItemList
End Function

Save the project and close the VB editor

保存项目并关闭VB编辑器
Under Queries, Create a new query in design view.

在查询下，在design视图中创建一个新的查询。
Switch to the SQL View.

切换到SQL视图。

Paste in this SQL:

在这个SQL粘贴:

SELECT 
    contact_id,
    name,
    LAST (Concat (contact_id, contact_type))  AS [contact_type]
FROM
    table1
GROUP BY
    contact_id,
    name
ORDER BY
    contact_id

Run the query (Press the red exclamation mark or just select the Datasheet View).

运行查询(按红色惊叹号或选择Datasheet视图)。
Done!

完成了!

#1