Loop code in a query?

[maxxev]

Hi,
I have some code as below which I would like to make more efficient, do you know how?

I'd also like to add in the "," character but not have it appear before or after if there is no allergen before or after.

Thanks in advance

CONTAINS LIST: [Q_SKU Allergens by Type].[BARLEY]+" " & [Q_SKU Allergens by Type].[CELERY]+" " & [Q_SKU Allergens by Type].[CRUSTACEAN]+" " & [Q_SKU Allergens by Type].[EGG]+" " & [Q_SKU Allergens by Type].[FISH]+" " & [Q_SKU Allergens by Type].[GLUTEN]+" " & [Q_SKU Allergens by Type].[WHEAT]+" " & [Q_SKU Allergens by Type].[MILK]+" " & [Q_SKU Allergens by Type].[MUSTARD]+" " & [Q_SKU Allergens by Type].[NUTS]+" " & [Q_SKU Allergens by Type].[OATS]+" " & [Q_SKU Allergens by Type].[PEANUTS]+" " & [Q_SKU Allergens by Type].[RYE]+" " & [Q_SKU Allergens by Type].[SESAME]+" " & [Q_SKU Allergens by Type].[SOYA]+" " & [Q_SKU Allergens by Type].[SULPHITES]

Note that the above is all pulling from a cross tab query (where I got the headings from). The original data is in a normalised Table.

If there is a way of doing the above without putting the data into a crosstab first, please also let me know.

e.g. the original table just has "full_product_code" and "Allergen Type" as the inputs.
The "Allergen Type" also is the ID of another table which has "Allergen Type" and "Translation" for foreign language labels.

i've attached the relationships if it helps.

Also the SQL for the final statement query is below:

SELECT [Q_SKU Allergens by Type].Full_Product_Code, [Q_SKU Allergens by Type].[Country names], IIf(IsNull([Q_SKU Allergens by Type].[NUTS]),[Q_Allergen Translations].[NUTS],Null)+" " & IIf(IsNull([Q_SKU Allergens by Type].[SESAME]),[Q_Allergen Translations].[SESAME],Null) AS [MAY CONTAIN LIST], IIf(IsNull([Q_SKU Allergens by Type].[VEGETARIAN]),Null,[Q_Allergen Translations].[VEGETARIAN])+" " & IIf(IsNull([Q_SKU Allergens by Type].[VEGAN]),Null,[Q_Allergen Translations].[VEGAN]) AS [SUITABLE FOR], [Q_SKU Allergens by Type].[BARLEY]+" " & [Q_SKU Allergens by Type].[CELERY]+" " & [Q_SKU Allergens by Type].[CRUSTACEAN]+" " & [Q_SKU Allergens by Type].[EGG]+" " & [Q_SKU Allergens by Type].[FISH]+" " & [Q_SKU Allergens by Type].[GLUTEN]+" " & [Q_SKU Allergens by Type].[WHEAT]+" " & [Q_SKU Allergens by Type].[MILK]+" " & [Q_SKU Allergens by Type].[MUSTARD]+" " & [Q_SKU Allergens by Type].[NUTS]+" " & [Q_SKU Allergens by Type].[OATS]+" " & [Q_SKU Allergens by Type].[PEANUTS]+" " & [Q_SKU Allergens by Type].[RYE]+" " & [Q_SKU Allergens by Type].[SESAME]+" " & [Q_SKU Allergens by Type].[SOYA]+" " & [Q_SKU Allergens by Type].[SULPHITES] AS [CONTAINS LIST]
FROM [Q_SKU Allergens by Type] LEFT JOIN [Q_Allergen Translations] ON [Q_SKU Allergens by Type].[Country names] = [Q_Allergen Translations].[Country names]
ORDER BY [Q_SKU Allergens by Type].Full_Product_Code, [Q_SKU Allergens by Type].[Country names];

Thanks again.