title: Present several row values in one row and multiple columns and avoid empty fields on Access reports
body: <p>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.</p>
  
  <p>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.</p>
  
  <p>Suppose I have table in Ms Access with following information:</p>
  
  <pre><code>| ProductID | AssignedColour |
  |-----------|----------------|
  | 1         | white          |
  | 1         | red            |
  | 1         | yellow         |
  | 2         | black          |
  | 2         | white          |
  | 3         | pink           |
  </code></pre>
  
  <p>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.</p>
  
  <p>My question is: <strong>How can I present all assigned colors in one row but have the color names split into separate columns?</strong></p>
  
  <p>The query results I want are as follows:</p>
  
  <pre><code>| ProductID | Colour1 | Colour2 | Colour3 | .... as many Columns as       |
  |           |         |         |         |      assigned in above table. |
  |-----------|---------|---------|---------|-------------------------------|
  | 1         | white   | red     | yellow  |                               |
  | 2         | black   | white   |         |                               |
  | 3         | pink    |         |         |                               |
  </code></pre>
  
  <p>I need to generate MS Access 2010 reports and present every product with only assigned colors (empty columns are not allowed).</p>
  
  <p>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.</p>
  
  <p>My solution gives the results as follows:</p>
  
  <pre><code>| ProductID | Colour1 | Colour2 | Colour3 | .... 250 columns with colors,   |
  |           |         |         |         |      but it can't be on reports.|
  |-----------|---------|---------|---------|---------------------------------|
  | 1         | white   | red     | yellow  |                                 |
  | 2         | white   | &lt;empty&gt; | &lt;empty&gt; |                                 |
  | 3         | &lt;empty&gt; | &lt;empty&gt; | &lt;empty&gt; |                                 |
  </code></pre>
  
  <p>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.</p>