concatenate

[johnlute]

I'm having real trouble with this and hope someone can help!

Access 2003.

SELECT IngredientMaster.IMNumber, Trim(Concatenate("SELECT
INGsAllergens
FROM tblINGsAllergens
WHERE IMNumber ="""" & [IMNumber] & """" ORDER BY
INGsAllergens")) AS Allergens
FROM IngredientMaster
WHERE (((IngredientMaster.IMNumber) In (SELECT IMNumber FROM
tblINGsAllergens)));

IMNumber is a numeric field set as Long Integer.

When I run the query it returns fine EXCEPT that it's concatenating
ALL records per each IMNumber. For example the folowing data:

IMNumber | Allergen
3 | Egg
3 | Fish
3 | Milk
4 | Peanuts
4 | Soybeans

returns like this:
3 | Egg, Fish, Milk, Peanuts, Soybeans
4 | Egg, Fish, Milk, Peanuts, Soybeans

Does anybody see what I need to change in my code? Thanks!

 

[johnlute]

I'm having real trouble with this and hope someone can help!

Access 2003.

SELECT IngredientMaster.IMNumber, Trim(Concatenate("SELECT
INGsAllergens
FROM tblINGsAllergens
WHERE IMNumber ="""" & [IMNumber] & """" ORDER BY
INGsAllergens")) AS Allergens
FROM IngredientMaster
WHERE (((IngredientMaster.IMNumber) In (SELECT IMNumber FROM
tblINGsAllergens)));

IMNumber is a numeric field set as Long Integer.

When I run the query it returns fine EXCEPT that it's concatenating
ALL records per each IMNumber. For example the folowing data:

IMNumber | Allergen
3 | Egg
3 | Fish
3 | Milk
4 | Peanuts
4 | Soybeans

returns like this:
3 | Egg, Fish, Milk, Peanuts, Soybeans
4 | Egg, Fish, Milk, Peanuts, Soybeans

Does anybody see what I need to change in my code? Thanks!

Please disregard. I found it. I needed to remove a couple of the "" in
this:
Allergens: Trim(Concatenate("SELECT INGsAllergens FROM
tblINGsAllergens WHERE IMNumber =" & [IMNumber] & " ORDER BY
INGsAllergens"))