Transform function

[billmark]

Hi
I have created the following query (UL) with fields (Clients, Products):
Clients Products
726 654
726 278
726 6100
7402 293
7402 8109
HUYT 654
HUYT 8109
HUYT 7440
HUYT 7991

and please advise how to use the Transform function to create the following query:
Clients Product1 Product2 Product3 Product4
726 654 278 6100
7402 293 8109
HUYT 654 8109 7440 7991

Transform First(UL.Clients) as First
Select UL.Client
From UL
Group by UL.Clients
Pivot UL.Products

Thanks

 

[jdraw]

see the concat function here, unless you really want the TRANSFORM for some reason,
it has function and sample

http://allenbrowne.com/func-concat.html

 

[billmark]

Hi Jdraw
Thanks for the reply. I really want to use TRANSFORM.

Regards

 

[vbaInet]

There's no Transform function in Access and the concat code is close enough to what you want to do. Even Transform in Excel will not get you what you described.

If you want to get it out as you've described then you need to:

1. Create a table with an extra field the new column headers, i.e. to save Product1, Product2, ... etc
2. There should also be a field for holding the numeric values.
3. Base your table on a recordset and for each row save into the temp table from step 1.
4. Create a crosstab query from your query in step 2 using Clients as your Row Heading, the numeric values will be the Value field, and the Column Heading will be the extra field you created in step 1.

As you can see it's not as straightforward as you probably had imagined.