How to transform data to crosstab-like format?

[amolin]

May i reach following function by Grid Query design or by SQL


Original:
==============================================
Factory 1-----------Product A
Factory 1-----------Product B
Factory 1-----------Product C
Factory 1-----------Product D


Factory 2-----------Product D
Factory 2-----------Product E
Factory 2-----------Product F

Factory 3-----------Product D
Factory 3-----------Product E
Factory 3-----------Product F


Result:
==============================================
Factory 1-----------Product A,Product B,Product C,Product D
Factory 2-----------Product D,Product E,Product F
Factory 3-----------Product D,Product E,Product F

 

[Alisa]

Just use the crosstab query wizard - it will be one of the choices when you create a new query.

 

[amolin]

Just use the crosstab query wizard - it will be one of the choices when you create a new query.

thank you for yor reply, but cross tab couldn't get what i want:

i need two column table, if by cross tab wizard, it will generate multi column dtable.

 

[Alisa]

What if you make the crosstab query, then make a new select query based on the crosstab query to concatenate the values into one column.

 

[amolin]

What if you make the crosstab query, then make a new select query based on the crosstab query to concatenate the values into one column.

Thank you Alisa;

If we have hundreds of products, then we need involve hundreds of fields involved in the Select query, for most time, the number of products name are not the same all the time.

Best Regards

 

[Mike375]

This may help

http://www.access-programmers.co.uk/forums/showthread.php?p=690960#post690960

Go down to about post 6 for Ken Higg and he has a link to a DB specimen he made. From memory I think it does in principle what you after.

 

[amolin]

Thank you, Mike;

They give a short code to achive this.

Seems no better method to get this done by SQL.

 

[Cosmos75]

Here's one way to do it in a query, but it can be very VERY SLOW for a lot of records.
DConcat function - Concatenating field values for records matching a query's GROUP / SORT BY or WHERE clause. (Old link)

I suppose you could use it to create a make-table query.