Very simple for you, Merge values into a variable 2

[Apprentice101]

I have the following table.

Client Store Products

1 houston shirt
2 austin jeans
3 dallas pijamas
2 austin socks
2 austin Sandals
3 dallas shoes


I need to write a query in Access that will display the data in the following way.

Client Store Products
1 Houston shirt
2 Austin jeans, Socks, Sandals
3 Dallas Pijamas, shoes


I tried, select and distinct and the help but cant find anything that will do it,
Can you please help me?

thanks a bunch!!!

 

[PHV]

Have a look here: faq701-4233

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Apprentice101]

thanks Phv,
it's a little too much for my level of knowledge, isnt there an easier way? like to build a query with an expression?

thanks for your help.

 

[PHV]

If NEVER more than 3 products ... (Note: this is SQL code)
SELECT A.Client, A.Store, A.Products & ", " + Min(B.Products) & ", " + Max(C.Products) AS Products
FROM (yourTable AS A
LEFT JOIN yourTable AS B ON A.Client = B.Client AND A.Store = B.Store AND A.Products < B.Products)
LEFT JOIN yourTable AS C ON B.Client = C.Client AND B.Store = C.Store AND B.Products < C.Products
WHERE A.Products = (SELECT Min(Products) FROM yourTable WHERE Client = A.Client AND Store = A.Store)
GROUP BY A.Client, A.Store, A.Products

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

There is a sample mdb of the concatenate function usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

If you don't understand code, there is no better time than the present to start. You have lots of people here to answer your questions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]