Question

我希望表1中的数据能够像2000年MS接入表所示。

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

我要回来:

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

我采用某种方式,例如使用组群、xml_path in sql,但它在接触烟.方面不做工作。

请就此向我提供指导。

Answer 1

这里有一条方式:

开放式视像主编:Tools --> Macro——>视觉基本主编(或Alt)

Insert a module and paste in this 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

www.un.org/Depts/DGACM/index_spanish.htm

Save the project and close the VB editor
在Queries下,在设计上形成新的查询。
Switch to the SQL View.

摘录:

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

www.un.org/Depts/DGACM/index_spanish.htm

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

友情链接