combine data in recordsets

[cdiltz]

I would like to have a module function in Access that can be run from either
a Button on a form or called in a Macro. This function will run on 2 tables.
A MasterItem table and PartItem table. There are many PartItem table records
for each related MasterItem table record. THESE tables are all in the
Access db.

Their is a field on each PartItem record that I need to concatonate the
string values into 1 string line on the MasterItem record. I'm not sure the
best approach
to writing the selecting join query in the function and where to put the
update
parts. Here is some sample data if you can help me get started.


MasterItem table has following fields and sample record:
{ItemCode}, {ItemStyle}, {ItemType}, {PartItemOptions}
2002, 2002X, MASTER , _______

PartItem table has following fields:
{ItemCode}, {ItemStyle}, {ItemType}, {ItemOption}
2002-01, 2002X, PART , 01
2002-02, 2002X, PART , 02
2002-03, 2002X, PART , 03


The Function will join the recordsets based on the ItemStyle field.
Need to string together the ItemOption value in the PartItem table
and update that to the MasterItem table record.
In this sample the string will become "01; 02; 03"

The final MasterItem table will be:
{ItemCode}, {ItemStyle}, {ItemType}, {PartItemOptions}
2002, 2002X, MASTER , 01; 02; 03

THANKS!!

 

[Frank]

Hope this helps -

tblMasterItems ( bytID, strCode, strDescription )
1, MI 1, MI 1 Description
2, MI 2, MI 2 Description
3, MI 3, MI 3 Description

tblPartItems ( bytID, strCode, strDescription )
1, PI 1, PI 1 Description
2, PI 2, PI 2 Description
3, PI 3, PI 3 Description

tblMasterItemsPartItems ( bytMasterItem, bytPartItem )
1 , 1
1 , 2
1 , 3
2 , 2
3 , 1
3 , 3

Join the tables tblMasterItemsPartItems.bytMasterItem to
tblMasterItems.bytID and tblMasterItemsPartItems.bytPartItem to
tblPartItems.bytID
MI 1, MI 1 Description, PI 1, PI 1 Description
MI 1, MI 1 Description, PI 2, PI 2 Description
MI 1, MI 1 Description, PI 3, PI 3 Description
MI 2, MI 2 Description, PI 2, PI 2 Description
MI 3, MI 3 Description, PI 1, PI 1 Description
MI 3, MI 3 Description, PI 3, PI 3 Description

Table tblMasterItemsPartItems is better than your PartItemOptions field.