Combining multiple columns into one row in one field

[Will]

I have a table, tblManinstructions 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 tblManinstructions). E.g. Code 1234 currently has 4
fields in tblManinstructions, 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 tblMaininstructions and
tblInstructions... for a report, perhaps. If you had a foreign key to
tblMaininstructions 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 tblMaininstructions. 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, tblManinstructions 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 tblManinstructions). E.g. Code 1234 currently has 4
fields in tblManinstructions, 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,
tblManInstructions 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 tblManinstructions 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*****@localhost.not> wrote in message
news:IQdye.326$Gv3.318@trnddc02...

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 tblMaininstructions and
tblInstructions... for a report, perhaps. If you had a foreign key to
tblMaininstructions 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 tblMaininstructions. 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, tblManinstructions 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 tblManinstructions). E.g. Code 1234 currently has
4 fields in tblManinstructions, 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