Is this possible in Access? I have done it in VFP but cannot figure
it out in Access:

I have a display-only form with various controls that reads in values
from several tables. There is also a "Notes" textbox that I would
like to populate by combining all notes records related to a
particular Item_ID and displaying them in one textbox (again for
display purposes only):

Tables are set up like this:
Item_Master has an item_id field (et al).

ProdNotesXref is a cross reference table and has an Item_id field and
a Note_id field. There are instances where a single item_id appears
in multiple rows, each pointng to a different note_id field.

ProdNotes has a Note_id field and the actual memo field with the
Notes.

ITEM_MASTER:
Item_id
001
<etc>

PRODNOTESXREF:
Item_id Note_id
001 178
001 224
001 316
002 178
002 076
003 316
003 271
<etc>

PRODNOTES:
Note_id Note
178 Item was made in Canary, Blue and Amber in 1925.
<...>
224 Was sold as a set with item #099.
<...>
316 Mold was sold to another company, who re-
released the item.
<...>

So the end result in the read-only text box would be the three lines
of notes in prodnotes whose note_id is associated with the item_id in
the xref table.

Thanks for any help you can provide.

Here are links (url) to three examples on concatenating a field from multiple
records into one field in one record of a query

Duane Hookom
http://www.rogersaccesslibrary.com/f...sts.asp?TID=16

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

The Access Web
http://www.mvps.org/access/modules/mdl0004.htm

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

skoalnreds wrote:
> Is this possible in Access? I have done it in VFP but cannot figure
> it out in Access:
>
> I have a display-only form with various controls that reads in values
> from several tables. There is also a "Notes" textbox that I would
> like to populate by combining all notes records related to a
> particular Item_ID and displaying them in one textbox (again for
> display purposes only):
>
> Tables are set up like this:
> Item_Master has an item_id field (et al).
>
> ProdNotesXref is a cross reference table and has an Item_id field and
> a Note_id field. There are instances where a single item_id appears
> in multiple rows, each pointng to a different note_id field.
>
> ProdNotes has a Note_id field and the actual memo field with the
> Notes.
>
> ITEM_MASTER:
> Item_id
> 001
> <etc>
>
> PRODNOTESXREF:
> Item_id Note_id
> 001 178
> 001 224
> 001 316
> 002 178
> 002 076
> 003 316
> 003 271
> <etc>
>
> PRODNOTES:
> Note_id Note
> 178 Item was made in Canary, Blue and Amber in 1925.
> <...>
> 224 Was sold as a set with item #099.
> <...>
> 316 Mold was sold to another company, who re-
> released the item.
> <...>
>
> So the end result in the read-only text box would be the three lines
> of notes in prodnotes whose note_id is associated with the item_id in
> the xref table.
>
> Thanks for any help you can provide.

On Jul 30, 2:07*pm, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> Here are links (url) to three examples on concatenating a field from multiple
> records into one field in one record of a query
>
> Duane Hookomhttp://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
>
> Allen Brownehttp://allenbrowne.com/func-concat.html
>
> The Access Webhttp://www.mvps.org/access/modules/mdl0004.htm
>
> John Spencer
> Access MVP 2002-2005, 2007-2010
> The Hilltop Institute
> University of Maryland Baltimore County
>
>
>
> skoalnreds wrote:
> > Is this possible in Access? *I have done it in VFP but cannot figure
> > it out in Access:
>
> > I have a display-only form with various controls that reads in values
> > from several tables. *There is also a "Notes" textbox that I would
> > like to populate by combining all notes records related to a
> > particular Item_ID and displaying them in one textbox (again for
> > display purposes only):
>
> > Tables are set up like this:
> > Item_Master has an item_id field (et al).
>
> > ProdNotesXref is a cross reference table and has an Item_id field and
> > a Note_id field. *There are instances where a single item_id appears
> > in multiple rows, each pointng to a different note_id field.
>
> > ProdNotes has a Note_id field and the actual memo field with the
> > Notes.
>
> > ITEM_MASTER:
> > Item_id
> > 001
> > <etc>
>
> > PRODNOTESXREF:
> > Item_id * * * * * * Note_id
> > 001 * * * * * * * * *178
> > 001 * * * * * * * * *224
> > 001 * * * * * * * * *316
> > 002 * * * * * * * * *178
> > 002 * * * * * * * * *076
> > 003 * * * * * * * * *316
> > 003 * * * * * * * * *271
> > <etc>
>
> > PRODNOTES:
> > Note_id * * * * * * Note
> > 178 * * * * * * * * * Item was made in Canary, Blue and Amber in 1925.
> > <...>
> > 224 * * * * * * * * * Was sold as a set with item #099.
> > <...>
> > 316 * * * * * * * * * Mold was sold to another company, who re-
> > released the item.
> > <...>
>
> > So the end result in the read-only text box would be the three lines
> > of notes in prodnotes whose note_id is associated with the item_id in
> > the xref table.
>
> > Thanks for any help you can provide.- Hide quoted text -
>
> - Show quoted text -

Perfect. Just what I was looking for...thank you!