Combining multiple columns into one row in one field

[Will]

I have a table, tblManinstructi ons with fields Code & InstructionID, one
Code can have many InstructionID. I also have tblinstructions (fields
instructionID & instruction). What I want to do is create a table with the
fields Code and Instruction - a combination of all instructions from the
instruction IDs in tblManinstructi ons). E.g. Code 1234 currently has 4
fields in tblManinstructi ons, instructionIDs 28, 43 & 76. The new table I
want to create will have one row with the instructions of Ids 28,43 & 76 in
one field, separated by commas. Can anyone help?

thanks

[Larry Linson]

I understand you have multiple fields with the same meaning (InstructionID)
in the main record. If I am wrong, please clarify. If I am correct in my
understanding, that is not a good idea because it will cause problems for
you, sooner or later.

For example, sometime , you'll have a need to join tblMaininstruct ions and
tblInstructions ... for a report, perhaps. If you had a foreign key to
tblMaininstruct ions in tblInstructins (if it is one to many) or a junction
table with foreign keys to both (if many to many), that would be easy. And,
when that comes up, expect to hear the same again from many sources.

You omitted an important part, which is the names of the multiple
InstructionID fields in tblMaininstruct ions. For brevity, I will use ID1,
ID2, ID3, and ID$ for this example:

ID1 & (", "+ ID2) & (", "+ ID3) & (", "+ ID4)

should give you the result that you want, presuming that if there are any
IDs, there will be an ID1. The + operator for concatenation results in a
Null if either of the operands is Null; the & operator treats Null as if it
were an empty string, so the result is not Null.

Larry Linson
Microsoft Access MVP

"Will" <Wi************ @hotmail.com> wrote in message
news:3i******** ****@individual .net...

I have a table, tblManinstructi ons with fields Code & InstructionID, one
Code can have many InstructionID. I also have tblinstructions (fields
instructionI D & instruction). What I want to do is create a table with the
fields Code and Instruction - a combination of all instructions from the
instruction IDs in tblManinstructi ons). E.g. Code 1234 currently has 4
fields in tblManinstructi ons, instructionIDs 28, 43 & 76. The new table I
want to create will have one row with the instructions of Ids 28,43 & 76 in
one field, separated by commas. Can anyone help?

thanks

[William]

Firstly, thanks for your help,
tblManInstructi ons is my link table and has two fields - ProductCode and
InstructionID. These fields are both foreign keys, linking to
tblInstructions & tblProducts. I'm not sure whether this is right, but it
is how I inherited it! I am trying to convert it to a table where the
relationship is one product to one instruction. The multiple InstructionID
fields in tblManinstructi ons all have the same name - InstructionID - there
are just multiple records for each Instruction. I am trying to join columns
together rather than rows.

"Larry Linson" <bo*****@localh ost.not> wrote in message
news:IQdye.326$ Gv3.318@trnddc0 2...

I understand you have multiple fields with the same meaning (InstructionID)
in the main record. If I am wrong, please clarify. If I am correct in my
understandin g, that is not a good idea because it will cause problems for
you, sooner or later.

For example, sometime , you'll have a need to join tblMaininstruct ions and
tblInstructions ... for a report, perhaps. If you had a foreign key to
tblMaininstruct ions in tblInstructins (if it is one to many) or a junction
table with foreign keys to both (if many to many), that would be easy.
And, when that comes up, expect to hear the same again from many sources.

You omitted an important part, which is the names of the multiple
InstructionID fields in tblMaininstruct ions. For brevity, I will use ID1,
ID2, ID3, and ID$ for this example:

ID1 & (", "+ ID2) & (", "+ ID3) & (", "+ ID4)

should give you the result that you want, presuming that if there are any
IDs, there will be an ID1. The + operator for concatenation results in a
Null if either of the operands is Null; the & operator treats Null as if
it were an empty string, so the result is not Null.

Larry Linson
Microsoft Access MVP

"Will" <Wi************ @hotmail.com> wrote in message
news:3i******** ****@individual .net...

I have a table, tblManinstructi ons with fields Code & InstructionID, one
Code can have many InstructionID. I also have tblinstructions (fields
instruction ID & instruction). What I want to do is create a table with
the fields Code and Instruction - a combination of all instructions from
the instruction IDs in tblManinstructi ons). E.g. Code 1234 currently has
4 fields in tblManinstructi ons, instructionIDs 28, 43 & 76. The new table
I want to create will have one row with the instructions of Ids 28,43 & 76
in one field, separated by commas. Can anyone help?

thanks