CODEWITHSUNDEEP
ms-accessconcatenation
BenRox
BenRox

Reputation: 41

Present several row values in one row and multiple columns and avoid empty fields on Access reports

First let me say that pivot solutions are good, but in this case can't be the final solution because the result is several empty columns (for values which don't match a particular record) and this can't be used on MS Access 2010 reports.

I believe this is common problem and a good solution would help many small developers who are just starting their adventure into VBA for Access.

Suppose I have table in Ms Access with following information:

| ProductID | AssignedColour |
|-----------|----------------|
| 1         | white          |
| 1         | red            |
| 1         | yellow         |
| 2         | black          |
| 2         | white          |
| 3         | pink           |

The problem is that in the table there are around 10,000 products which use around 250 unique color names (and list of colors can increase). Also, one particular ProductID might have assigned to it an undefined number of colors.

My question is: How can I present all assigned colors in one row but have the color names split into separate columns?

The query results I want are as follows:

| ProductID | Colour1 | Colour2 | Colour3 | .... as many Columns as       |
|           |         |         |         |      assigned in above table. |
|-----------|---------|---------|---------|-------------------------------|
| 1         | white   | red     | yellow  |                               |
| 2         | black   | white   |         |                               |
| 3         | pink    |         |         |                               |

I need to generate MS Access 2010 reports and present every product with only assigned colors (empty columns are not allowed).

I found some solutions based on pivot tables, but this results in 250 dynamically created columns. So I don't know how can I show only specific columns with only colors matching for a particular product.

My solution gives the results as follows:

| ProductID | Colour1 | Colour2 | Colour3 | .... 250 columns with colors,   |
|           |         |         |         |      but it can't be on reports.|
|-----------|---------|---------|---------|---------------------------------|
| 1         | white   | red     | yellow  |                                 |
| 2         | white   | <empty> | <empty> |                                 |
| 3         | <empty> | <empty> | <empty> |                                 |

MS Access does not allow for the presentation of dynamic columns. Also generating such reports takes very long time. It gives empty fields, something that is unacceptable on professional reports. I am considering if I should put query values into some temporary table first and then concatenate the values, but I don't know how to make that table. Maybe somebody has a sample database which could cover all of these aspects.

Upvotes: 4

Views: 2586

Answers (2)

Rejeeb R
Rejeeb R

Reputation: 1

Try this

SELECT t.Filed1,
       STUFF(ISNULL((SELECT ', ' + x.Filed2
                FROM TableName x
                WHERE x.Filed1= t.Filed1
                GROUP BY x.Filed2
             FOR XML PATH (''), TYPE).value('.','VARCHAR(max)'), ''), 1, 2, '') 
  FROM TableName t
GROUP BY t.Filed1

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123779

Start by creating a query that assigns a ranked name to each colour by ProductID

SELECT 
    t1.ProductID,
    t1.AssignedColour,
    "Colour" & Format(COUNT(*),"000") AS ColourName
FROM
    ProductColours AS t1
    INNER JOIN
    ProductColours AS t2
        ON t1.ProductID = t2.ProductID
            AND t1.AssignedColour >= t2.AssignedColour
GROUP BY 
    t1.ProductID,
    t1.AssignedColour

returning

ProductID  AssignedColour  ColourName
---------  --------------  ----------
        1  red             Colour001 
        1  white           Colour002 
        1  yellow          Colour003 
        2  black           Colour001 
        2  white           Colour002 
        3  pink            Colour001

Now wrap that in a crosstab query to PIVOT on ColourName

TRANSFORM First(AssignedColour) AS whatever
SELECT 
    ProductID
FROM
    (
        SELECT 
            t1.ProductID,
            t1.AssignedColour,
            "Colour" & Format(COUNT(*),"000") AS ColourName
        FROM
            ProductColours AS t1
            INNER JOIN
            ProductColours AS t2
                ON t1.ProductID = t2.ProductID
                    AND t1.AssignedColour >= t2.AssignedColour
        GROUP BY 
            t1.ProductID,
            t1.AssignedColour
    ) AS ColourNames
GROUP BY ProductID
PIVOT ColourName

which returns

ProductID  Colour001  Colour002  Colour003
---------  ---------  ---------  ---------
        1  red        white      yellow   
        2  black      white               
        3  pink

Upvotes: 5

Related Questions