Merging many rows to one

[Paweł]

Hi

I would like to ask for a hint of writing a query that collects data
from several rows and turn them on one line.

As a result of a crosstab table is created that contains the data
scattered across several columns.

Data

ID| K1 | K2 | K3 | K4
----------------------------------------------
1 | clone1 | | |
2 | Sycamore2 | clone1 | |
1 | | | |
3 | | | | beech2
1 | pine2 | | |

The expected result is:

ID|K1 | K2 | K3 | K4
----------------------------------------------
1 | clone1 | pine2 | |
2 | Sycamore2 | clone1 | |
3 | beech2 | | |

At the moment, if I do another query cross it missing result "pine2"
because the resulting table is taken only the first row for a given id,
if it is possible to do (without creating some macros and programming)
query that collects the data and stores it in the first free cells of a row?

Thank you in advance for your help, best regards Paul

[Ron Weiner]

Hi
I would like to ask for a hint of writing a query that collects data from
several rows and turn them on one line.
As a result of a crosstab table is created that contains the data scattered
across several columns.
Data
ID| K1 | K2 | K3 | K4
----------------------------------------------
1 | clone1 | | |
2 | Sycamore2 | clone1 | |
1 | | | |
3 | | | | beech2
1 | pine2 | | |
ID|K1 | K2 | K3 | K4
----------------------------------------------
1 | clone1 | pine2 | |
2 | Sycamore2 | clone1 | |
3 | beech2 | | |
At the moment, if I do another query cross it missing result "pine2" because
the resulting table is taken only the first row for a given id, if it is
possible to do (without creating some macros and programming) query that
collects the data and stores it in the first free cells of a row?
Thank you in advance for your help, best regards Paul

You are gonna need to use some VBA to do this in Access. See Allen
Brown's execlent example at http://allenbrowne.com/func-concat.html.

Ron W