CODEWITHSUNDEEP
ms-accessvba
Taya
Taya

Reputation: 135

How to group after two fields and concatenate information from third field in query

I want to group data in ONE table after two columns (Numbers) and concatenate the information from a third column (short text) for the grouped results.

I am a non programming end user of Access with only little experience in SQL and no experience in VBA but I managed to follow the steps in this other question which already gets me half way to solving my own problem.

Concatenate multiple rows in one field in Access?

That´s why I will reuse the example data used in that post.

One probably just needs to tweak the code slightly for everything to work as intended.

The slightly changed example data from a referenced question looks like this:

Table "YourTable"

Year | Order Number | Product Types
2014 |       100001 | TV
2014 |       100001 | Phone
2016 |       100001 | Internet
2014 |       100002 | Phone
2014 |       100002 | Phone
2014 |       100003 | TV
2014 |       100003 | Internet
2015 |       100003 | Phone
2015 |       100003 | Notebook

For each available combination of Year and OrderNumber I want all corresponding differing entries in the column "product Types" listed separated by a slash or semicolon.

To do this for only one column to group by (Order Number) you can find the solution under the above linked question in the answer by HansUp: https://stackoverflow.com/a/12428291/3954188

He uses the function "Concatenate values from related records" provided by Allen Browne to achieve the desired result and also provides the final query as an example. Everything works fine for grouping after one column using these resources.

How would I modify the query to get it working the way I´d like it to or is this impossible and needs another solution?

Please post the modified function code and/or query if possible. I managed to implement the function and the example solution from the other question but I´m not well versed in using SQL or VBA.

(I´m using Win 7, 64bit and MS Office 2013)

Upvotes: 2

Views: 1159

Answers (2)

HansUp
HansUp

Reputation: 97100

Include Year and Order Number in your query's GROUP BY. Then you want to concatenate the Product Types values within each of those groups.

I stored your sample data in an Access 2010 table named YourTable. With those data, this is the output from the query below ...

Year Order Number Expr1
---- ------------ --------------
2014       100001 Phone;TV
2014       100002 Phone;Phone
2014       100003 Internet;TV
2015       100003 Notebook;Phone
2016       100001 Internet

SELECT
    y.Year,
    y.[Order Number],
    ConcatRelated
        (
            '[Product Types]',
            'YourTable',
            '[Year]=' & y.Year & ' AND [Order Number]=' & y.[Order Number],
            '[Product Types]',
            ';'
        ) AS Expr1
FROM YourTable AS y
GROUP BY
    y.Year,
    y.[Order Number];

Upvotes: 1

Mahesh Madushanka
Mahesh Madushanka

Reputation: 2998

YOU CAN GET IT using following SQL (ON MYSQL)

SELECT Year , Order Number ,GROUP_CONCAT( Product Types) FROM TABLE GROUP BY Year , Order Number

Upvotes: 0

Related Questions