Merge multiple rows in one field in Access?

Question

Possible duplicate:
Ms access request: string concatenation through request

I have a table in which there are many columns, but two are of interest: order number and product type. The table currently has several types of products for each order. If a customer ordered a telephone service, a TV service, and an Internet service, then there will be three entries - one for each service, but they all have the same order number. I want to create a reference table to store a concatenated string with all the services ordered by the client. That way I can generalize my data using this more logical method. I am using a standard Access 2010 database.

**Current table:** Order Number | Product Types 100001 | TV 100001 | Phone 100001 | Internet 100002 | Phone 100003 | TV 100003 | Internet 

Desired Reference Table

 100001 | TV/Phone/Internet 100002 | Phone 100003 | TV/Internet 

Answer 1

Allen Brown provides a feature that you might find useful for this: Combine values ​​from related records . Save this function code in the standard module.

 SELECT DISTINCT [Order Number], ConcatRelated("[Product Types]", "YourTable", "[Order Number] = " & [Order Number], "[Product Types]", "/" ) AS All_Product_Types FROM YourTable; 

I tested this query in Access 2007 with your sample data stored in a table called "YourTable". He returned the results you requested. However, this only works from an access session. If you want to run this request from external Access (for example, from ASP), user-defined functions are not available, therefore you will receive the ConcatRelated() error message not recognized.

That way, you can use a query to retrieve concatenated values ​​whenever you need it. However, if you store these concatenated values, they may quickly not synchronize with changes in the data in the base table.

Answer 2

You should not create a reference table that combines records. This is a database denormalization.

You can try the crosstab as shown below, but I have not tested it. You can read here for more information.

 TRANSFORM First([Product Types]) AS Product SELECT [Order Number], First([Product Types]) FROM CurrentTable GROUP [Order Number] 

Answer 3

If I understand the question, you ask how to get order numbers only for those orders that have a TV, phone and Internet. If you are simply interested in these order numbers, you can run a query such as:

 SELECT Distinct Table1.OrderNumber FROM (Select OrderNumber from Table1 where [product types]= "Internet") AS i INNER JOIN ((Select OrderNumber from Table1 where [product types]="Phone") AS p INNER JOIN ((Select OrderNumber from Table1 Where [product types]= "TV") AS tv INNER JOIN Table1 ON tv.OrderNumber = Table1.OrderNumber) ON p.OrderNumber = Table1.OrderNumber) ON i.OrderNumber = Table1.OrderNumber; 

Answer 4

As noted oneday, when in an early post on SO it is easier with ADO: Request example:

  SELECT [Order Number], ConcatADO("SELECT [Product Types] FROM Orders WHERE [Order Number]=" & [Order Number],", "," : ") AS Cat FROM Orders GROUP BY [Order Number], 2; 

Function Using ADO

 Function ConcatADO(strSQL As String, strColDelim, _ strRowDelim, ParamArray NameList() As Variant) Dim rs As New ADODB.Recordset Dim strList As String On Error GoTo Proc_Err If strSQL <> "" Then rs.Open strSQL, CurrentProject.Connection strList = rs.GetString(, , strColDelim, strRowDelim) strList = Mid(strList, 1, Len(strList) - Len(strRowDelim)) Else strList = Join(NameList, strColDelim) End If ConcatADO = strList Exit Function Proc_Err: ConcatADO = "***" & UCase(Err.Description) End Function