Prev: match values
Next: Using IIF to pass query criteria
From: johnlute on 2 Apr 2010 10:00
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!
From: johnlute on 2 Apr 2010 10:08
On Apr 2, 10:00 am, johnlute <jl...(a)marzetti.com> wrote:
> 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"))
 | 
Pages: 1
Prev: match values
Next: Using IIF to pass query criteria