Prev: gong
Next: Subform with selected item
From: M on 6 May 2010 11:04
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: Jeff Boyce on 6 May 2010 14:38
You provided an example of how you want the results output ... but no idea
where the data is coming FROM.

"How" depends on "what".

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"M" <M(a)discussions.microsoft.com> wrote in message
news:D49D580C-723E-445E-9186-DBAFCEBE4389(a)microsoft.com...
> 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: Jeff Boyce on 6 May 2010 14:40
Perhaps I misread your notation...

Are you saying that TB1 is a table, and TB2 is another table?

If so, it looks like you could join them on UserID in a query to get all the
items belonging to a user.

To get the concatenated list of items, consider using a report, fed by the
query. Also do a search online for a concatenation routine that Duane H.
created...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"M" <M(a)discussions.microsoft.com> wrote in message
news:D49D580C-723E-445E-9186-DBAFCEBE4389(a)microsoft.com...
> 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


 | 
Pages: 1
Prev: gong
Next: Subform with selected item