Prev: Sub report formatting
Next: Sorting in reports
From: M on 6 May 2010 09:41
I would like to know how to write SQL Statement to get the result below

Name Item
Ann Item1 Item2
Ben Item3

TB1
UserID Name
1 Ann
2 Ben

TB2
ItemID UserID Item
1 1 Item1
2 1 Item2
3 2 Item3


if add 4 1 Item4
the result will become
Name Item
Ann Item1 Item2 Item4
Ben Item3


Any idea, I'm using MS Access

Thanks
From: John W. Vinson on 6 May 2010 13:36
On Thu, 6 May 2010 06:41:01 -0700, M <M(a)discussions.microsoft.com> wrote:

>I would like to know how to write SQL Statement to get the result below
>
>Name Item
>Ann Item1 Item2
>Ben Item3
>
>TB1
>UserID Name
>1 Ann
>2 Ben
>
>TB2
>ItemID UserID Item
>1 1 Item1
>2 1 Item2
>3 2 Item3
>
>
>if add 4 1 Item4
>the result will become
>Name Item
>Ann Item1 Item2 Item4
>Ben Item3

If there's an arbitrary number of items, and you want them all concatenated in
the same field, this is rather difficult. You can do it with the help of some
VBA code; see

http://www.mvps.org/access/modules/mdl0004.htm

This code gives a comma separated list but it's simple to replace the comma
with a blank.
--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Sub report formatting
Next: Sorting in reports