Concatenate multiple rows in one field in Access?

Question

Possible Duplicate:
Ms Access Query: Concatenating Rows through a query

I have a table that has many columns, but two of interest: Order Number and Product Type. Currently the table has multiple Product Types per Order. If the customer ordered phone service, TV service and Internet service, then there would be three records - one for each service but all having the same order number. I want to create a reference table to store a concatenated string with all of the services the customer ordered. This way I can summarize my data using this more logical method. I'm 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

You should not do this. You are denormalizing your database. — Kermit, Sep 14 '12 at 15:57
Is there a better way to do this? Ultimately I just want to be able to report at the "TV/Phone/Internet" level. — Kyle Hawke, Sep 14 '12 at 15:58
denormalize at the presentation level (report,) not the data storage level — Beth, Sep 14 '12 at 15:59
I think you want this to be a `View` not a `Table`. It will get the same result for your reporting purposes but the data will stay in a logical organization — Brad, Sep 14 '12 at 16:23
@njk He is not Denormalizing the DB if all he is doing is creating View of the data and concatenating it accordingly. — GoldBishop, Sep 14 '12 at 17:24
@KyleHawke As stated previously, just create a View that concatenates all of the Values together. Since you obviously wont be using this information to Update the table, its totally safe. — GoldBishop, Sep 14 '12 at 17:25

HansUp · Answer 1 · 2012-09-14T16:52:43.297

Allen Browne provides a function which you may find useful for this: Concatenate values from related records. Save that function's code in a standard module.

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

I tested that query in Access 2007 with your sample data saved in a table named "YourTable". It returned the results you asked for. However, this only works from within an Access session. If you wanted to run this query from outside Access (like from ASP), user-defined functions are not available, so you would get an error about ConcatRelated() not recognized.

So you can use a query to retrieve the concatenated values whenever you need them. However if you store those concatenated values, they can quickly be out of sync with changes to the base table's data.

score 1 · Answer 2 · answered Sep 14 '12 at 16:20

If I understand the question, you're asking how to get the order numbers of just those orders who have TV, AND phone, AND internet. If you're just interested in those order numbers you could run a query like:

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;

score 1 · Answer 3 · answered Sep 14 '12 at 17:41

As was pointed out by onedaywhen in an early post on SO, this is easier with ADO: Sample query:

   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

score 0 · Answer 4 · answered Sep 14 '12 at 16:01

0

You should not create a reference table that concatenates records. That is denormalizing the database.

You can try a crosstab query like 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]

answered Sep 14 '12 at 16:01

Kermit

33,827
13
85
121

3

Normalization is unimportant on a table that he's building for reporting purposes. – Jim Sep 14 '12 at 16:20
The OP shouldn't be a using a non-normalized table for reporting purposes. He can opt for a `VIEW`. – Kermit Sep 14 '12 at 16:27
There are few reasons for him not to. In fact, storing the data in static, denormalized table can be preferable in some situations. For example, if the data doesn't change much, or it doesn't need to be up the minute, he only has to query the rest of his tables once in awhile, instead of every time the report is run. It also makes the SQL much easier, as he can use several queries to build his dataset instead of trying to squish the logic into a single query. – Jim Sep 14 '12 at 16:32
@Jim It should not be done on the database layer. – Kermit Sep 14 '12 at 17:50

Concatenate multiple rows in one field in Access?

4 Answers4

Linked

Related