0
Select [Trader],[Reference] , [Type of Establishment], [Fruit Sold], [Stat Licence]
from Tablex
where [Type of Establishment]='D/03288'
and [Stat Licence]='Licenced'

Figure 1 - Sample results

Trader,Reference , Type of Establishment, Fruit Sold, Stat Licnece 
Lady Daves Foxes,  D/03288, Licenced, Bananas , Y_T_177
Lady Daves Foxes,  D/03288, Licenced, Apples , Y_T_177`
Lady Daves Foxes,  D/03288, Licenced, Pears , Y_T_177`

Now how do I get the result set below in Microsoft Access changing the sql view?

Figure 1 - What I want ?

Trader,Reference , Type of Establishment, Fruit Sold, Stat Licnece

Lady Daves Foxes, D/03288, Licenced, Bananas,Apples,Pear , Y_T_177

Suppose I need the pivot the Fruit sold column somehow ?

Prahalad Gaggar
  • 11,389
  • 16
  • 53
  • 71
James Khan
  • 773
  • 2
  • 18
  • 46
  • If you can use vba functions the first answer to this post gives a solution: [Microsoft Access condense multiple lines in a table](http://stackoverflow.com/questions/5174362/microsoft-access-condense-multiple-lines-in-a-table/5174843#5174843) – jpw Jul 15 '13 at 10:23
  • 1st choice is modifying sql using the SQL View window. Thanks for the post. Don't have much experience implementing these functions is sql so looks scary. – James Khan Jul 15 '13 at 10:32

1 Answers1

1

you are looking for a group_concat, but this does not exist in Access.

Nevertheless, there are workarounds which have been discussed here before.

E.g. is there a group_concat function in ms-access?

Hope this helps R

Community
  • 1
  • 1
szeta
  • 589
  • 1
  • 5
  • 21